make a copy of database to local

[alanchinese]

i am a db2 newbie. we have a server hosting a db2/6000 database that
restricts the use of backup, generate ddl. i wonder if there is a
simple way to transfer the database structure and data into my laptop,
on which db2/nt was installed.
i had tried to use DTS service from microsoft sql server. it didn't
work.
any suggestion?
any help would be appreciated.
alan.

[Phil Sherman]

If you have access to the database through the control center on your
laptop, is there a "Generate DDL" option for the database? If so, you
can use it to generate the DDL you are looking for.

Data can be copied using:
INSERT INTO local_table
SELECT * FROM remote_table

Good luck!
Phil Sherman

alanchinese wrote:

i am a db2 newbie. we have a server hosting a db2/6000 database that
restricts the use of backup, generate ddl. i wonder if there is a
simple way to transfer the database structure and data into my laptop,
on which db2/nt was installed.
i had tried to use DTS service from microsoft sql server. it didn't
work.
any suggestion?
any help would be appreciated.
alan.

[Larry]

alanchinese wrote:

i am a db2 newbie. we have a server hosting a db2/6000 database that
restricts the use of backup, generate ddl. i wonder if there is a
simple way to transfer the database structure and data into my laptop,
on which db2/nt was installed.
i had tried to use DTS service from microsoft sql server. it didn't
work.
any suggestion?
any help would be appreciated.
alan.

You will need to use the db2move utility.

Larry Edelstein

[Phil Sherman]

Correction:

INSERT .... SELECT ......

won't work across databases.

You should be able to export the data to your laptop then import it to a
local table. If you can't export the data, then you could select it to a
file and use that as input to load. You'll need to build delimiters or
use fixed length output from the select. Watch out for long character
and varchar columns if you try to use fixed length records - they can
cause very large files.

All of these options require you to "catalog" the remote database on
your laptop.

The "Generate DDL" option is accessed using a right-click on the
database name in the control center's list of databases.

Phil Sherman
Phil Sherman wrote:

If you have access to the database through the control center on your
laptop, is there a "Generate DDL" option for the database? If so, you
can use it to generate the DDL you are looking for.

Data can be copied using:
INSERT INTO local_table
SELECT * FROM remote_table

Good luck!
Phil Sherman

alanchinese wrote:

i am a db2 newbie. we have a server hosting a db2/6000 database that
restricts the use of backup, generate ddl. i wonder if there is a
simple way to transfer the database structure and data into my laptop,
on which db2/nt was installed.
i had tried to use DTS service from microsoft sql server. it didn't
work.
any suggestion?
any help would be appreciated.
alan.

[Rhino]

"alanchinese" <al*********@yahoo.com> wrote in message
news:11*********************@h76g2000cwa.googlegro ups.com...

i am a db2 newbie. we have a server hosting a db2/6000 database that
restricts the use of backup, generate ddl. i wonder if there is a
simple way to transfer the database structure and data into my laptop,
on which db2/nt was installed.
i had tried to use DTS service from microsoft sql server. it didn't
work.
any suggestion?
any help would be appreciated.
alan.

Have a look at the db2move command. It is documented in the Information
Center; just enter the following in the Search box and click on the GO
button: db2move

--
Rhino

[alanchinese]

generate ddl is not allowed in control center.
export a table is not working too.
i tried db2move, it complains: SQL0551N "xxx" does not have the
privilege to perform operation "BIND" on object "NULLID.DB2MOVE".
SQLSTATE=42501
looking for bind file in current directory...
bind file: DB2MOVE.BND .... could not be opened.

actually, is db2move doing some sort of xml transferring from one
database to another?

Rhino wrote:

"alanchinese" <al*********@yahoo.com> wrote in message
news:11*********************@h76g2000cwa.googlegro ups.com...

i am a db2 newbie. we have a server hosting a db2/6000 database that
restricts the use of backup, generate ddl. i wonder if there is a
simple way to transfer the database structure and data into my laptop,
on which db2/nt was installed.
i had tried to use DTS service from microsoft sql server. it didn't
work.
any suggestion?
any help would be appreciated.
alan.

Have a look at the db2move command. It is documented in the Information
Center; just enter the following in the Search box and click on the GO
button: db2move

--
Rhino

[Larry]

alanchinese wrote:

generate ddl is not allowed in control center.
export a table is not working too.
i tried db2move, it complains: SQL0551N "xxx" does not have the
privilege to perform operation "BIND" on object "NULLID.DB2MOVE".
SQLSTATE=42501
looking for bind file in current directory...
bind file: DB2MOVE.BND .... could not be opened.

actually, is db2move doing some sort of xml transferring from one
database to another?

Rhino wrote:

"alanchinese" <al*********@yahoo.com> wrote in message
news:11*********************@h76g2000cwa.googleg roups.com...

i am a db2 newbie. we have a server hosting a db2/6000 database that
restricts the use of backup, generate ddl. i wonder if there is a
simple way to transfer the database structure and data into my laptop,
on which db2/nt was installed.
i had tried to use DTS service from microsoft sql server. it didn't
work.
any suggestion?
any help would be appreciated.
alan.

Have a look at the db2move command. It is documented in the Information
Center; just enter the following in the Search box and click on the GO
button: db2move

--
Rhino

No ... it is using the db2look utility to generate the DDL and the
export utility to move the data. Probably requires additional
authorities. Best thing to do is to see your DBA.

Larry Edelstein

[situ]

woul'nt it be possible using some third party tool like "DATA AQUA
STUDIO".

[alanchinese]

DBA will not give me those access for just duplicating the database
into my laptop.
lol~
i am seeking an alternate way....
:(

Larry wrote:

alanchinese wrote:

generate ddl is not allowed in control center.
export a table is not working too.
i tried db2move, it complains: SQL0551N "xxx" does not have the
privilege to perform operation "BIND" on object "NULLID.DB2MOVE".
SQLSTATE=42501
looking for bind file in current directory...
bind file: DB2MOVE.BND .... could not be opened.

actually, is db2move doing some sort of xml transferring from one
database to another?

Rhino wrote:

"alanchinese" <al*********@yahoo.com> wrote in message
news:11*********************@h76g2000cwa.googleg roups.com...

i am a db2 newbie. we have a server hosting a db2/6000 database that
restricts the use of backup, generate ddl. i wonder if there is a
simple way to transfer the database structure and data into my laptop,
on which db2/nt was installed.
i had tried to use DTS service from microsoft sql server. it didn't
work.
any suggestion?
any help would be appreciated.
alan.
Have a look at the db2move command. It is documented in the Information
Center; just enter the following in the Search box and click on the GO
button: db2move

--
Rhino

No ... it is using the db2look utility to generate the DDL and the
export utility to move the data. Probably requires additional
authorities. Best thing to do is to see your DBA.

Larry Edelstein

[Phil Sherman]

It appears that you are atempting to make a copy of corporate data for
your own (business?) needs. As long as you can read the data, this is
possible but you have a number of non-database issues that need to be
addressed:
1. Why does the data owner NOT want copies of the data made?
2. What laws apply to controlling access to the data? Which of them are
likely to be broken when you have your own copy of the data?
3. What are your potential consequences to you and your employer of
having the data on your laptop? Imagine that it's stolen and the data
becomes available to the thief. Worse, what happens if you catch a virus
that downloads the data to a nefarious individual?
4. Will you need to keep the data current? if so, how will you update
it? When you update it, how much of your workday will it take?

alanchinese wrote:

DBA will not give me those access for just duplicating the database
into my laptop.
lol~
i am seeking an alternate way....
:(

Larry wrote:

alanchinese wrote:

generate ddl is not allowed in control center.
export a table is not working too.
i tried db2move, it complains: SQL0551N "xxx" does not have the
privilege to perform operation "BIND" on object "NULLID.DB2MOVE".
SQLSTATE=42501
looking for bind file in current directory...
bind file: DB2MOVE.BND .... could not be opened.

actually, is db2move doing some sort of xml transferring from one
database to another?

Rhino wrote:
"alanchinese" <al*********@yahoo.com> wrote in message
news:11*********************@h76g2000cwa.googl egroups.com...
>i am a db2 newbie. we have a server hosting a db2/6000 database that
>restricts the use of backup, generate ddl. i wonder if there is a
>simple way to transfer the database structure and data into my laptop,
>on which db2/nt was installed.
>i had tried to use DTS service from microsoft sql server. it didn't
>work.
>any suggestion?
>any help would be appreciated.
>alan.
>

Have a look at the db2move command. It is documented in the Information
Center; just enter the following in the Search box and click on the GO
button: db2move

--
Rhino

No ... it is using the db2look utility to generate the DDL and the
export utility to move the data. Probably requires additional
authorities. Best thing to do is to see your DBA.

Larry Edelstein

[alanchinese]

phil, i understand your concern.
here are my response:
1. it's a security thing. we are not allowed to connect to the db while
we are out of the office. i am not using the data or data model for
personal use or business use.
2. all data are testing, i am a developer. i want to have a copy of the
structure so that i can run the testing environment while i am not in
the office.
3. the only thing potentially will be stolen is the data model, not the
data. but the laptop i am using now is for development. if the laptop
got stolen, the developing app will be stolen too.
4. i don't need to keep the data current. however, i would like the
data model to be current. thereafter, if i am having a updated code, i
can test against the updated data model.

Phil Sherman wrote:

It appears that you are atempting to make a copy of corporate data for
your own (business?) needs. As long as you can read the data, this is
possible but you have a number of non-database issues that need to be
addressed:
1. Why does the data owner NOT want copies of the data made?
2. What laws apply to controlling access to the data? Which of them are
likely to be broken when you have your own copy of the data?
3. What are your potential consequences to you and your employer of
having the data on your laptop? Imagine that it's stolen and the data
becomes available to the thief. Worse, what happens if you catch a virus
that downloads the data to a nefarious individual?
4. Will you need to keep the data current? if so, how will you update
it? When you update it, how much of your workday will it take?

alanchinese wrote:

DBA will not give me those access for just duplicating the database
into my laptop.
lol~
i am seeking an alternate way....
:(

Larry wrote:

alanchinese wrote:
generate ddl is not allowed in control center.
export a table is not working too.
i tried db2move, it complains: SQL0551N "xxx" does not have the
privilege to perform operation "BIND" on object "NULLID.DB2MOVE".
SQLSTATE=42501
looking for bind file in current directory...
bind file: DB2MOVE.BND .... could not be opened.

actually, is db2move doing some sort of xml transferring from one
database to another?

Rhino wrote:
>"alanchinese" <al*********@yahoo.com> wrote in message
>news:11*********************@h76g2000cwa.googl egroups.com...
>
>
>>i am a db2 newbie. we have a server hosting a db2/6000 database that
>>restricts the use of backup, generate ddl. i wonder if there is a
>>simple way to transfer the database structure and data into my laptop,
>>on which db2/nt was installed.
>>i had tried to use DTS service from microsoft sql server. it didn't
>>work.
>>any suggestion?
>>any help would be appreciated.
>>alan.
>>
>
>Have a look at the db2move command. It is documented in the Information
>Center; just enter the following in the Search box and click on the GO
>button: db2move
>
>--
>Rhino
No ... it is using the db2look utility to generate the DDL and the
export utility to move the data. Probably requires additional
authorities. Best thing to do is to see your DBA.

Larry Edelstein

[Larry]

alanchinese wrote:

DBA will not give me those access for just duplicating the database
into my laptop.
lol~
i am seeking an alternate way....
:(

Larry wrote:

alanchinese wrote:

generate ddl is not allowed in control center.
export a table is not working too.
i tried db2move, it complains: SQL0551N "xxx" does not have the
privilege to perform operation "BIND" on object "NULLID.DB2MOVE".
SQLSTATE=42501
looking for bind file in current directory...
bind file: DB2MOVE.BND .... could not be opened.

actually, is db2move doing some sort of xml transferring from one
database to another?

Rhino wrote:
"alanchinese" <al*********@yahoo.com> wrote in message
news:11*********************@h76g2000cwa.googl egroups.com...
>i am a db2 newbie. we have a server hosting a db2/6000 database that
>restricts the use of backup, generate ddl. i wonder if there is a
>simple way to transfer the database structure and data into my laptop,
>on which db2/nt was installed.
>i had tried to use DTS service from microsoft sql server. it didn't
>work.
>any suggestion?
>any help would be appreciated.
>alan.
>

Have a look at the db2move command. It is documented in the Information
Center; just enter the following in the Search box and click on the GO
button: db2move

--
Rhino

No ... it is using the db2look utility to generate the DDL and the
export utility to move the data. Probably requires additional
authorities. Best thing to do is to see your DBA.

Larry Edelstein

Then, you will need to make a business case to have access to that data.
You have no other options. You have a business problem ... not a
technology problem.

Larry Edelstein

[Phil Sherman]

Item 2 in your response means that you are want this for business use -
your business of being an application developer for this application.
Larry (06/01/2006 7:29pm) hit the nail on the head with his response.

There's something amiss here; THe database architecture and data are
very rigidly controlled while you are allowed to remove a laptop from
the office containing an application that is being developed?? Surely,
the application contains information about the tables it accesses which
should reveal something about the data structure. If all application
developers work with laptops like this, then even more of the
architecture could be comprimised.

I'd just let it go and accept my employer's desire to prohibit me from
working when I'm not in the office. If this is not your employer's
intent, then you need, as Larry sugggested, to make a business case for
changing the rules. I know this is possible because many years ago, I
worked in an environment where it took 2-3 days to get approval to get
access to a data file for a single test. Each subsequent test required
the same approval process. A well prepared business case showing the
true cost of limited data access and a proposal to develop valid,
"scrubbed" test data enabled development of a test environment that all
of the programmers were able to use without restrictions.
Phil Sherman

alanchinese wrote:

phil, i understand your concern.
here are my response:
1. it's a security thing. we are not allowed to connect to the db while
we are out of the office. i am not using the data or data model for
personal use or business use.
2. all data are testing, i am a developer. i want to have a copy of the
structure so that i can run the testing environment while i am not in
the office.
3. the only thing potentially will be stolen is the data model, not the
data. but the laptop i am using now is for development. if the laptop
got stolen, the developing app will be stolen too.
4. i don't need to keep the data current. however, i would like the
data model to be current. thereafter, if i am having a updated code, i
can test against the updated data model.

Phil Sherman wrote:

It appears that you are atempting to make a copy of corporate data for
your own (business?) needs. As long as you can read the data, this is
possible but you have a number of non-database issues that need to be
addressed:
1. Why does the data owner NOT want copies of the data made?
2. What laws apply to controlling access to the data? Which of them are
likely to be broken when you have your own copy of the data?
3. What are your potential consequences to you and your employer of
having the data on your laptop? Imagine that it's stolen and the data
becomes available to the thief. Worse, what happens if you catch a virus
that downloads the data to a nefarious individual?
4. Will you need to keep the data current? if so, how will you update
it? When you update it, how much of your workday will it take?