Copying tables from one DB2 ssid to another

Bill Brown

Copying tables from one DB2 ssid to another
I'm a general sysprog who takes care of DB2 and other stuff at our shop.
Sorry if this seems really elementary. I need to move a couple of tables
from our DB2 production ssid to my systems test ssid. Our applications
DBA's do this pretty regularly, but I had some problems with the canned
job they pointed me to. I did something like this once about 10 years
ago and seem to have forgotten what I did. I think I used DSN1COPY with
the OBIDXLAT parm, but I'm not sure. If someone can point me in the
right direction I would GREATLY appreciate it.

Thanks,
Bill Brown
Sysprog
Texas Tech University

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Manas (Cognizant) Biswal

Re: Copying tables from one DB2 ssid to another
(in response to Bill Brown)

Bill,

If it is only a couple of tables, why not do an unload/load.

Thanks,
Manas

"It is not the strongest of the species that survives, nor the most intelligent; it is the one that is most adaptable to change". - Charles Darwin


________________________________

From: DB2 Data Base Discussion List on behalf of Brown, Bill
Sent: Fri 12/2/2005 2:42 PM
To: [login to unmask email]
Subject: [DB2-L] Copying tables from one DB2 ssid to another


I'm a general sysprog who takes care of DB2 and other stuff at our shop. Sorry if this seems really elementary. I need to move a couple of tables from our DB2 production ssid to my systems test ssid. Our applications DBA's do this pretty regularly, but I had some problems with the canned job they pointed me to. I did something like this once about 10 years ago and seem to have forgotten what I did. I think I used DSN1COPY with the OBIDXLAT parm, but I'm not sure. If someone can point me in the right direction I would GREATLY appreciate it.

Thanks,
Bill Brown
Sysprog
Texas Tech University
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm


This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information.
If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
Any unauthorised review, use, disclosure, dissemination, forwarding, printing or copying of this email or any action taken in reliance on this e-mail is strictly
prohibited and may be unlawful.

Visit us at http://www.cognizant.com

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Bill Brown

Re: Copying tables from one DB2 ssid to another
(in response to Manas (Cognizant) Biswal)
I was thinking the last time I did this, that there was some special
requirement because the table was being moved from one ssid to another.
I remember looking up the dbid and obid and using them in the jobs I
used to move the data from one ssid to the other. I thought that was
because I was going from one ssid to the other and dbid and obid had to
be changed. But it was the first time I had ever done anything with DB2
so maybe there was some other reason I needed to do that or I didn't
need to do it at all. Too long ago.

Thanks. I'll look at unload/load.

-Bill

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]
On Behalf Of Biswal, Manas (Cognizant)
Sent: Friday, December 02, 2005 2:04 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Copying tables from one DB2 ssid to another


Bill,

If it is only a couple of tables, why not do an unload/load.

Thanks,
Manas

"It is not the strongest of the species that survives, nor the
most intelligent; it is the one that is most adaptable to change". -
Charles Darwin


________________________________

From: DB2 Data Base Discussion List on behalf of Brown, Bill
Sent: Fri 12/2/2005 2:42 PM
To: [login to unmask email]
Subject: [DB2-L] Copying tables from one DB2 ssid to another


I'm a general sysprog who takes care of DB2 and other stuff at
our shop. Sorry if this seems really elementary. I need to move a couple
of tables from our DB2 production ssid to my systems test ssid. Our
applications DBA's do this pretty regularly, but I had some problems
with the canned job they pointed me to. I did something like this once
about 10 years ago and seem to have forgotten what I did. I think I used
DSN1COPY with the OBIDXLAT parm, but I'm not sure. If someone can point
me in the right direction I would GREATLY appreciate it.

Thanks,
Bill Brown
Sysprog
Texas Tech University

------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm
This e-mail and any files transmitted with it are for the sole use of
the intended recipient(s) and may contain confidential and privileged
information.
If you are not the intended recipient, please contact the sender by
reply e-mail and destroy all copies of the original message.
Any unauthorised review, use, disclosure, dissemination, forwarding,
printing or copying of this email or any action taken in reliance on
this e-mail is strictly
prohibited and may be unlawful.

Visit us at http://www.cognizant.com


------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Kumar Vaddadi/EIG

Re: Copying tables from one DB2 ssid to another
(in response to Bill Brown)
If the Tables are big... Unload & Loads takes lots of time compared to
DSN1COPY.. For example of DSN1COPY for your requirements, pls see Example
2 in the DB2 Utility guide ( hope that helps). DSN1COPY is quick & fast as
it works on underlying VSAM principle but it has some prerequisites( pls
read them before use it).

************* Thanks & Regards *******************
Vaddadi Shyam Kumar
IBM Db2 Certified Database Administrator
Data Management Group
The Economical Insurance Group, Waterloo.
Reach me @ 519-570-8500 ext 2242
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Happiness is when what you think, what you say, and what you do are in
harmony. ----- Mahatma Gandhi
******************************************************************************************************************************


|---------+---------------------------->
| | "Biswal, Manas |
| | (Cognizant)" |
| | <[login to unmask email]|
| | NIZANT.COM> |
| | Sent by: DB2 Data|
| | Base Discussion |
| | List |
| | <[login to unmask email]|
| | ORG> |
| | |
| | |
| | 12/02/2005 03:03 |
| | PM |
| | Please respond to|
| | DB2 Database |
| | Discussion list |
| | at IDUG |
| | |
|---------+---------------------------->
>------------------------------------------------------------------------------------------------------------------------------|
| |
| To: [login to unmask email] |
| cc: |
| Subject: Re: [DB2-L] Copying tables from one DB2 ssid to another |
>------------------------------------------------------------------------------------------------------------------------------|




Bill,

If it is only a couple of tables, why not do an unload/load.

Thanks,
Manas

"It is not the strongest of the species that survives, nor the most
intelligent; it is the one that is most adaptable to change". - Charles
Darwin


From: DB2 Data Base Discussion List on behalf of Brown, Bill
Sent: Fri 12/2/2005 2:42 PM
To: [login to unmask email]
Subject: [DB2-L] Copying tables from one DB2 ssid to another

I'm a general sysprog who takes care of DB2 and other stuff at our shop.
Sorry if this seems really elementary. I need to move a couple of tables
from our DB2 production ssid to my systems test ssid. Our applications
DBA's do this pretty regularly, but I had some problems with the canned job
they pointed me to. I did something like this once about 10 years ago and
seem to have forgotten what I did. I think I used DSN1COPY with the
OBIDXLAT parm, but I'm not sure. If someone can point me in the right
direction I would GREATLY appreciate it.

Thanks,
Bill Brown
Sysprog
Texas Tech University
---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged
information.
If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
Any unauthorised review, use, disclosure, dissemination, forwarding, printing or copying of this email or any action taken in reliance on this
e-mail is strictly
prohibited and may be unlawful.

Visit us at http://www.cognizant.com


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

[login to unmask email]

Re: Copying tables from one DB2 ssid to another
(in response to Kumar Vaddadi/EIG)
Bill,
If you want to automate the process, (all ISPF panel driven) you may wish to
visit: www.recoveryknowledge.com
It will not only automate the unload/load process to another db2id or the
same db2id but you can implement a job into production status and let it run on a
regular basic. The good thing about it is you can go back as far as you ran
your first unload with the product because it keep all the information it need
for the load in DB2 tables.

HTH
Ed.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Roland Schiradin

Re: Copying tables from one DB2 ssid to another
(in response to DB2information@AOL.COM)
How about the Cross-Loader ? It also depends on the size of data.

Roland


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Brown, Bill
Sent: Friday, December 02, 2005 8:43 PM
To: [login to unmask email]
Subject: [DB2-L] Copying tables from one DB2 ssid to another


I'm a general sysprog who takes care of DB2 and other stuff at our shop. Sorry if this seems really elementary. I need to move a couple of tables from our DB2 production ssid to my systems test ssid. Our applications DBA's do this pretty regularly, but I had some problems with the canned job they pointed me to. I did something like this once about 10 years ago and seem to have forgotten what I did. I think I used DSN1COPY with the OBIDXLAT parm, but I'm not sure. If someone can point me in the right direction I would GREATLY appreciate it.



---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mark McCormack

Copying tables from one DB2 ssid to another
(in response to Roland Schiradin)
Bill,

You have two choices, and variations on those choices. I will list two
options.
1. Unload prod table, then load into test table
2. Use a full image copy of the prod table to populate the test table via
DSN1COPY

option 1:
a. You will need to take the time to unload the prod table.
b. You can use pgm=DSNTIAUL or the DB2 unload utility. Both produce a data
file and a load stmt.
The format of the data file is different depending on which program
you use.
c. You may want to modify the load stmt. The column definitions are
accurate.
I usually change the control parameters. See example below.

option 2:
a. Determine the internal ID numbers of the db/ts/tb in each subsystem.
b. Run DSN1COPY to process the prod copy file, changing the ID numbers
c. Rebuild all indices on the table.

My examples assume a non-partitioned tablespace containing one table. I
assume a shrlevel reference full copy file. I assume no referential
integrity use. If your case is different, modifications are needed. Let
me know if my assumptions are wrong.

Mark

more on option1:
1- I use this kind of load stmt
LOAD LOG NO REUSE REPLACE
SORTDEVT SYSDA SORTNUM 32 NOCOPYPEND
STATISTICS TABLE(ALL) INDEX(ALL) KEYCARD
INTO TABLE creator.tbname
( -- column definitions -- )

more on option 2:
additional requirement:
The table structures for prod and test must be identical. If columns have
been added via ALTER, then a following reorg is required.

SQL to find dbid,psid,obid - run on both prod and test

//* IN FOLLOWING QUERY: ORDER BY 1, 2 --> SORT INTO NAME ORDER
//* ORDER BY 5, 6, 7 --> SORT INTO NUMERIC ORDER

SELECT A.DBNAME, B.TSNAME, B.CREATOR, B.NAME AS TBNAME,
A.DBID, A.PSID, B.OBID,
HEX(A.DBID) AS HEXDBID, HEX(A.PSID) AS HEXPSID,
HEX(B.OBID) AS HEXOBID
FROM SYSIBM.SYSTABLESPACE A, SYSIBM.SYSTABLES B
WHERE A.DBNAME = B.DBNAME AND
A.NAME = B.TSNAME AND
A.DBID <> 0 AND
A.DBNAME = 'dbname'
A.NAME = 'tsname'
ORDER BY 5, 6, 7 WITH UR;

Sample JCL for DSN1COPY and REBUILD INDEX

//* ************************************************************
//STOPTS EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT),
// PARM='DSN SYSTEM(ssid)'
//STEPLIB DD .............
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
-STOP DB(dbname) SPACE(tsname)
/*
//* ************************************************************
//DSN1COPY EXEC PGM=DSN1COPY,COND=(4,LT),
// PARM='FULLCOPY,OBIDXLAT,RESET'
//STEPLIB DD .............
//SYSPRINT DD SYSOUT=*
//SYSUT1 DD DISP=OLD,DSN=dsn of full copy file (not an inline copy)
//SYSUT2 DD DISP=OLD,DSN=hilvl.DSNDBC.dbname.tsname.I0001.A001
//* NOTE: IF YOU USE ONLINE REORG, IT MIGHT BE J0001 NOT I0001
//SYSXLAT DD *
SDDD,TDDD
SPPP,TPPP
SOOO,TOOO
/*
//* SDDD = DBID OF SOURCE TABLESPACE TDDD = DBID OF TARGET TABLESPACE
//* SPPP = PSID OF SOURCE TABLESPACE TPPP = PSID OF TARGET TABLESPACE
//* SOOO = OBID OF SOURCE TABLE TOOO = OBID OF TARGET TABLE
//* ************************************************************
//STARTTS EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT),
// PARM='DSN SYSTEM(ssid)'
//STEPLIB DD .............
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
-START DB(dbname) SPACE(tsname) ACC(RW)
/*
//**************************************************************
//REBUILD EXEC PGM=DSNUTILB,COND=(4,LT),
// PARM='ssid,REBUILD.INDEX,'
//STEPLIB DD .............
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//UTPRINT DD SYSOUT=*
//SYSIN DD *
REBUILD INDEX (ALL) TABLESPACE dbname.tsname
SORTDEVT SYSDA SORTNUM 32 SORTKEYS
/*

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm