Sql to load prod data into test

teldb2kals

Sql to load prod data into test
Hi Omar,

You need a Union to do this.

(Or, the other option is, do it as you have done, and use some edit macro to split the line into two).

Try :

SELECT stmt FROM

(
SELECT DISTINCT NAME, 'SELECT * FROM prod ENVIRONMENT.'||NAME||' ;' as stmt
FROM SYSIBM.SYSTABLES
WHERE DBNAME LIKE 'PRODUCTION environment'
AND TYPE = 'T'
AND CARD > 0

UNION

SELECT DISTINCT NAME, 'INSERT INTO '||NAME||' ' as stmt
FROM SYSIBM.SYSTABLES
WHERE DBNAME LIKE 'PRODUCTION environment'
AND TYPE = 'T'
AND CARD > 0
) A

ORDER BY NAME, STMT

WITH UR;

Regards,
Kals


"Ali, Omar" <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
30/12/2005 09:19
Please respond to DB2 Database Discussion list at IDUG

To: [login to unmask email]
cc:
Subject: [DB2-L]


Do any one know a simple way to write this sql;
I am trying to perp. For a refresh insert into my test environment The sql that works is as follow;

SELECT DISTINCT 'SELECT * FROM prod ENVIRONMENT.'||NAME||' ;'
FROM SYSIBM.SYSTABLES
WHERE DBNAME LIKE 'PRODUCTION environment'
AND TYPE = 'T'
AND CARD > 0
--ORDER BY DBNAME
WITH UR;

This is the result of the above sql;

SELECT * FROM prod environment.table name ;
SELECT * FROM prod environment.table name ;
SELECT * FROM prod environment.table name ;
SELECT * FROM prod environment.table name ;
SELECT * FROM prod environment.table name ;
SELECT * FROM prod environment.table name ;
SELECT * FROM prod environment.table name ;


But I can't get the insert to work,

SELECT DISTINCT 'SELECT * FROM ENVIRONMENT.'||NAME||' ;'
'INSERT INTO '||NAME||' '
FROM SYSIBM.SYSTABLES
WHERE DBNAME LIKE 'PRODUCTION%'
AND TYPE = 'T'
AND CARD > 0
--ORDER BY DBNAME
WITH UR;

THIS SQL SHOULD BE LIKE THIS,

INSERT INTO TEST ENVIRONMENT.TBNAME
SELECT * FROM prod environment.table name ;
INSERT INTO TEST ENVIRONMENT.TBNAME
SELECT * FROM prod environment.table name ;
INSERT INTO TEST ENVIRONMENT.TBNAME
SELECT * FROM prod environment.table name ;


Any ideas on how to get this to work in spufi ?
Thanks
Jr. dba



Omar Ali
Jr.Database Admin
x0729

---------------------------------------------------------------------------------
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

Doyle Mark

Re: Sql to load prod data into test
(in response to teldb2kals)
Omar,
Just to piggyback on what Kals wrote, His SQL assumes no RI, and no
Identity columns. If that is your situation, his SQL is what you need.
If, however, you have identity columns generated always the SQL gets
more complex (see below). If you need to insert in an RI structure,
contact me direct, as that sql (undocumented) is > 600 lines long.

Mark

-- Generate insert into table (column list) Select _column list_ from
table SQL (usable in DB2 z/os V7+)
--
-- FIRST LINE: INSERT INTO TABLE NAME (
--
SELECT
CAST(' INSERT INTO TEST.' CONCAT TB.NAME
CONCAT '(' AS CHAR(80))
,NAME , 0, 0
FROM SYSIBM.SYSTABLES TB
WHERE TB.DBNAME IN ( 'DNAP0001') -- OR YOUR CRITERIA
AND TB.TYPE = 'T'
UNION ALL
--
-- SECOND LINE: EITHER COLUMN NAME (FIRST COLUMN IN TABLE, OR
-- 2ND COL, IF 1ST IS IDENTITY
-- GENERATED ALWAYS)
-- OR ,COLUMN NAME (ALL OTHERS)
--
SELECT
CAST(
CASE WHEN CO.DEFAULT = 'I'
THEN '--' -- COMMENT OUT INDENTITY COL NAME
ELSE ' ' -- OTHERWISE NOTHING
END CONCAT
CASE WHEN CO.COLNO = 2 AND C2.DEFAULT = 'I' OR CO.COLNO = 1
THEN ' ' -- DO NOT PUT COMMA BEFORE 1ST COLUMN
-- DO NOT PUT COMMA BEFORE 2ND COLUMN
-- IF 1ST COLUMN IS IDENTITY
ELSE ' ,' -- OTHERWISE PUT A COMMA BEFORE NAME
END CONCAT CO.NAME
AS CHAR(80))
,CO.TBNAME , 1, CO.COLNO
FROM SYSIBM.SYSTABLES TB, SYSIBM.SYSCOLUMNS CO, SYSIBM.SYSCOLUMNS C2
WHERE TB.DBNAME IN ( 'DNAP0001') -- OR YOUR CRITERIA
AND TB.TYPE = 'T'
AND TB.NAME = CO.TBNAME
AND TB.NAME = C2.TBNAME
AND TB.CREATOR = CO.TBCREATOR
AND TB.CREATOR = C2.TBCREATOR
AND C2.COLNO = 1
UNION ALL
--
-- THIRD LINE: ) SELECT
--
SELECT
CAST( ' ) SELECT ' AS CHAR(80))
,NAME ,2 ,0
FROM SYSIBM.SYSTABLES TB
WHERE TB.DBNAME IN ( 'DNAP0001') -- OR YOUR CRITERIA
AND TB.TYPE = 'T'
UNION ALL
--
-- FOURTH LINE: EITHER COLUMN NAME (FIRST COLUMN IN TABLE, OR
-- 2ND COL, IF 1ST IS IDENTITY
-- GENERATED ALWAYS)
-- OR ,COLUMN NAME (ALL OTHERS)
--
SELECT
CAST(
CASE WHEN CO.DEFAULT = 'I'
THEN '--' -- COMMENT OUT INDENTITY COL NAME
ELSE ' ' -- OTHERWISE NOTHING
END CONCAT
CASE WHEN CO.COLNO = 2 AND C2.DEFAULT = 'I' OR CO.COLNO = 1
THEN ' ' -- DO NOT PUT COMMA BEFORE 1ST COLUMN
-- DO NOT PUT COMMA BEFORE 2ND COLUMN
-- IF 1ST COLUMN IS IDENTITY
ELSE ' ,' -- OTHERWISE PUT A COMMA BEFORE NAME
END CONCAT CO.NAME
AS CHAR(80))
,CO.TBNAME , 3, CO.COLNO
FROM SYSIBM.SYSTABLES TB, SYSIBM.SYSCOLUMNS CO, SYSIBM.SYSCOLUMNS C2
WHERE TB.DBNAME IN ( 'DNAP0001') -- OR YOUR CRITERIA
AND TB.TYPE = 'T'
AND TB.NAME = CO.TBNAME
AND TB.NAME = C2.TBNAME
AND TB.CREATOR = CO.TBCREATOR
AND TB.CREATOR = C2.TBCREATOR
AND C2.COLNO = 1
UNION ALL
--
-- FIFTH LINE: FROM TABLE NAME
--
SELECT
CAST(
' FROM ' CONCAT RTRIM(TB.CREATOR) CONCAT '.'
CONCAT TB.NAME CONCAT ';'
AS CHAR(80))
,NAME , 4, 0
FROM SYSIBM.SYSTABLES TB
WHERE TB.DBNAME IN ( 'DNAP0001') -- OR YOUR CRITERIA
AND TB.TYPE = 'T'
UNION ALL
--
-- SIXTH LINE: COMMIT (OPTIONAL)
--
SELECT
CAST(
' COMMIT;'
AS CHAR(80))
,NAME , 5, 0
FROM SYSIBM.SYSTABLES TB
WHERE TB.DBNAME IN ( 'DNAP0001') -- OR YOUR CRITERIA
AND TB.TYPE = 'T'
ORDER BY 2,3,4;


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of teldb2kals
Sent: Thursday, December 29, 2005 2:55 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Sql to load prod data into test


Hi Omar,

You need a Union to do this.

(Or, the other option is, do it as you have done, and use some edit
macro to split the line into two).

Try :

SELECT stmt FROM

(
SELECT DISTINCT NAME, 'SELECT * FROM prod ENVIRONMENT.'||NAME||' ;' as
stmt
FROM SYSIBM.SYSTABLES
WHERE DBNAME LIKE 'PRODUCTION environment'
AND TYPE = 'T'
AND CARD > 0

UNION

SELECT DISTINCT NAME, 'INSERT INTO '||NAME||' ' as stmt
FROM SYSIBM.SYSTABLES
WHERE DBNAME LIKE 'PRODUCTION environment'
AND TYPE = 'T'
AND CARD > 0
) A

ORDER BY NAME, STMT

WITH UR;

Regards,
Kals

---------------------------------------------------------------------------------
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

Omar Ali

Re: Sql to load prod data into test
(in response to Doyle Mark)
Thanks Guys,
For all the help/advise
And have a happy new year

Omar Ali
Jr.Database Admin
x0729

-----Original Message-----
From: Doyle Mark [mailto:[login to unmask email]
Sent: Thursday, December 29, 2005 4:48 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Sql to load prod data into test

Omar,
Just to piggyback on what Kals wrote, His SQL assumes no RI, and no
Identity columns. If that is your situation, his SQL is what you need.
If, however, you have identity columns generated always the SQL gets
more complex (see below). If you need to insert in an RI structure,
contact me direct, as that sql (undocumented) is > 600 lines long.

Mark

-- Generate insert into table (column list) Select _column list_ from
table SQL (usable in DB2 z/os V7+)
--
-- FIRST LINE: INSERT INTO TABLE NAME (
--
SELECT
CAST(' INSERT INTO TEST.' CONCAT TB.NAME
CONCAT '(' AS CHAR(80))
,NAME , 0, 0
FROM SYSIBM.SYSTABLES TB
WHERE TB.DBNAME IN ( 'DNAP0001') -- OR YOUR CRITERIA
AND TB.TYPE = 'T'
UNION ALL
--
-- SECOND LINE: EITHER COLUMN NAME (FIRST COLUMN IN TABLE, OR
-- 2ND COL, IF 1ST IS IDENTITY
-- GENERATED ALWAYS)
-- OR ,COLUMN NAME (ALL OTHERS)
--
SELECT
CAST(
CASE WHEN CO.DEFAULT = 'I'
THEN '--' -- COMMENT OUT INDENTITY COL NAME
ELSE ' ' -- OTHERWISE NOTHING
END CONCAT
CASE WHEN CO.COLNO = 2 AND C2.DEFAULT = 'I' OR CO.COLNO = 1
THEN ' ' -- DO NOT PUT COMMA BEFORE 1ST COLUMN
-- DO NOT PUT COMMA BEFORE 2ND COLUMN
-- IF 1ST COLUMN IS IDENTITY
ELSE ' ,' -- OTHERWISE PUT A COMMA BEFORE NAME
END CONCAT CO.NAME
AS CHAR(80))
,CO.TBNAME , 1, CO.COLNO
FROM SYSIBM.SYSTABLES TB, SYSIBM.SYSCOLUMNS CO, SYSIBM.SYSCOLUMNS C2
WHERE TB.DBNAME IN ( 'DNAP0001') -- OR YOUR CRITERIA
AND TB.TYPE = 'T'
AND TB.NAME = CO.TBNAME
AND TB.NAME = C2.TBNAME
AND TB.CREATOR = CO.TBCREATOR
AND TB.CREATOR = C2.TBCREATOR
AND C2.COLNO = 1
UNION ALL
--
-- THIRD LINE: ) SELECT
--
SELECT
CAST( ' ) SELECT ' AS CHAR(80))
,NAME ,2 ,0
FROM SYSIBM.SYSTABLES TB
WHERE TB.DBNAME IN ( 'DNAP0001') -- OR YOUR CRITERIA
AND TB.TYPE = 'T'
UNION ALL
--
-- FOURTH LINE: EITHER COLUMN NAME (FIRST COLUMN IN TABLE, OR
-- 2ND COL, IF 1ST IS IDENTITY
-- GENERATED ALWAYS)
-- OR ,COLUMN NAME (ALL OTHERS)
--
SELECT
CAST(
CASE WHEN CO.DEFAULT = 'I'
THEN '--' -- COMMENT OUT INDENTITY COL NAME
ELSE ' ' -- OTHERWISE NOTHING
END CONCAT
CASE WHEN CO.COLNO = 2 AND C2.DEFAULT = 'I' OR CO.COLNO = 1
THEN ' ' -- DO NOT PUT COMMA BEFORE 1ST COLUMN
-- DO NOT PUT COMMA BEFORE 2ND COLUMN
-- IF 1ST COLUMN IS IDENTITY
ELSE ' ,' -- OTHERWISE PUT A COMMA BEFORE NAME
END CONCAT CO.NAME
AS CHAR(80))
,CO.TBNAME , 3, CO.COLNO
FROM SYSIBM.SYSTABLES TB, SYSIBM.SYSCOLUMNS CO, SYSIBM.SYSCOLUMNS C2
WHERE TB.DBNAME IN ( 'DNAP0001') -- OR YOUR CRITERIA
AND TB.TYPE = 'T'
AND TB.NAME = CO.TBNAME
AND TB.NAME = C2.TBNAME
AND TB.CREATOR = CO.TBCREATOR
AND TB.CREATOR = C2.TBCREATOR
AND C2.COLNO = 1
UNION ALL
--
-- FIFTH LINE: FROM TABLE NAME
--
SELECT
CAST(
' FROM ' CONCAT RTRIM(TB.CREATOR) CONCAT '.'
CONCAT TB.NAME CONCAT ';'
AS CHAR(80))
,NAME , 4, 0
FROM SYSIBM.SYSTABLES TB
WHERE TB.DBNAME IN ( 'DNAP0001') -- OR YOUR CRITERIA
AND TB.TYPE = 'T'
UNION ALL
--
-- SIXTH LINE: COMMIT (OPTIONAL)
--
SELECT
CAST(
' COMMIT;'
AS CHAR(80))
,NAME , 5, 0
FROM SYSIBM.SYSTABLES TB
WHERE TB.DBNAME IN ( 'DNAP0001') -- OR YOUR CRITERIA
AND TB.TYPE = 'T'
ORDER BY 2,3,4;


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of teldb2kals
Sent: Thursday, December 29, 2005 2:55 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Sql to load prod data into test


Hi Omar,

You need a Union to do this.

(Or, the other option is, do it as you have done, and use some edit
macro to split the line into two).

Try :

SELECT stmt FROM

(
SELECT DISTINCT NAME, 'SELECT * FROM prod ENVIRONMENT.'||NAME||' ;' as
stmt
FROM SYSIBM.SYSTABLES
WHERE DBNAME LIKE 'PRODUCTION environment'
AND TYPE = 'T'
AND CARD > 0

UNION

SELECT DISTINCT NAME, 'INSERT INTO '||NAME||' ' as stmt
FROM SYSIBM.SYSTABLES
WHERE DBNAME LIKE 'PRODUCTION environment'
AND TYPE = 'T'
AND CARD > 0
) A

ORDER BY NAME, STMT

WITH UR;

Regards,
Kals

----------------------------------------------------------------------------
-----
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

-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally
privileged and/or exempt from disclosure pursuant to Massachusetts
law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and
delete the material from any computer.

---------------------------------------------------------------------------------
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