{Blank Subject Dec 29, 2005}

Omar Ali

{Blank Subject Dec 29, 2005}
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






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

Richard Fazio

Re:
(in response to Omar Ali)
I don't believe this is supported.

However, the IBM Load utility does have the "Cross load feature".
(your LOAD JCL here)
//SYSIN DD *
EXEC SQL
DECLARE C0001 CURSOR FOR
SELECT * FROM PROD.TABLE;
ENDEXEC
LOAD DATA INCURSOR(C0001)
LOG NO NOCOPYPEND REPLACE INTO TABLE TEST.TABLE
STATISTICS TABLE(ALL) INDEX(ALL)

Although, you cannot run with SHRLEVEL CHANGE :(

Best of luck,
faz

Rich Fazio
Information Architecture

TransUnion, LLC
Marketing Services, 8th Floor
555 West Adams St. Chicago, IL 60661
Phone (312) 985-3270 Fax (312) 466-6453

Talk to teach - Listen to learn


>>> [login to unmask email] 2005-12-29 2:19:26 PM >>>

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.TBNAMESELECT * 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 ?ThanksJr. dba Omar AliJr.Database
Adminx0729



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

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

Yuan Terry Chao

Re:
(in response to Basivi Inaganti)
Try below, and, on result, right-shift off excess columns using
combination of 'BNDS' and '))nnn' to get what you want:

SELECT 'INSERT INTO PROD.ENV.'||NAME,DBNAME,NAME,1
FROM SYSIBM.SYSTABLES
WHERE DBNAME LIKE 'test%'
AND TYPE = 'T'
AND CARD > 0
UNION
SELECT 'SELECT * FROM TEST.ENV.'||NAME||';',DBNAME,NAME,2
FROM SYSIBM.SYSTABLES
WHERE DBNAME LIKE 'test%'
AND TYPE = 'T'
AND CARD > 0
ORDER BY 2,3,4 WITH UR ;

Good luck and Happy New Year.





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


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


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

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

Basivi Inaganti

Re:
(in response to Richard Fazio)
Hi, Try the following.
SELECT DISTINCT 'INSERT INTO '||NAME||' ',
'SELECT * FROM ENVIRONMENT.'||NAME||' ;'
Thanks,
Basivi.




"Ali, Omar" <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
12/29/2005 03:19 PM
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


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

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