|
View previous topic :: View next topic
|
| Author |
Message |
apsar.kogatam
Warnings : 1
New User
Joined: 27 Jun 2008
Posts: 33
Location: CHENNAI
|
|
|
|
Hi Team,
From production daily to test region load card and datasets will be xcomed.To update the test tables I need to schedule a Jcl.
Can you please provide me some idea to code a Jcl which needs to load/refresh the test db2 tables with production data.I know to load the data in to table.But i am looking the optimised way to code the jcl instead of having several load steps for more tables. |
|
| Back to top |
|
 |
guptae
Moderator
Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India
|
|
|
|
Hello Apsar,
You can use crossload to load data from production to test. But you need to load the tables in correct order depending upon ref constraints etc. |
|
| Back to top |
|
|
apsar.kogatam
Warnings : 1
New User
Joined: 27 Jun 2008
Posts: 33
Location: CHENNAI
|
|
|
|
Hi Ekta,
Can you please explain what is crossload and how to use it with Jcl. |
|
| Back to top |
|
|
guptae
Moderator
Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India
|
|
|
|
Hi Apsar,
From the FM
| Quote: |
| This function enables you to use a single LOAD job to transfer data from one location to another location or from one table to another table at the same location. |
Eg:
| Code: |
//SYSIN DD *
EXEC SQL
DECLARE C001 CURSOR FOR
SELECT * FROM A51DD2P1.PNASCO.AGENT
ENDEXEC
LOAD DATA INCURSOR (C001)
ENFORCE NO REPLACE REUSE LOG NO NOCOPYPEND
INTO TABLE PNASCPRS.AGENT
EXEC SQL
DECLARE C002 CURSOR FOR
SELECT * FROM A51DD2P1.PNASCO.AGENT_ASSN
ENDEXEC
LOAD DATA INCURSOR (C002)
ENFORCE NO REPLACE REUSE LOG NO NOCOPYPEND
INTO TABLE PNASCPRS.AGENT_ASSN
|
Let me know if you have any questions |
|
| Back to top |
|
|
apsar.kogatam
Warnings : 1
New User
Joined: 27 Jun 2008
Posts: 33
Location: CHENNAI
|
|
|
|
Hi Ekta,
From the above code i understand that cursor and Load utility is used to load the data.But I cannot use the select statement here since i have only control card and data for load datasets from production which i need to use in test region to load the data to tables. |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Hi Apsar,
| Quote: |
| since i have only control card and data for load datasets from production which i need to use in test region to load the data to tables. |
Are you saying that you already have control card for the LOAD utility and datasets UNLOADed from production to be LOADed in development subsystem.
Can you please elaborate, on why you cannot use the code provided by guptae, because i think it will work.
Sushanth |
|
| Back to top |
|
|
apsar.kogatam
Warnings : 1
New User
Joined: 27 Jun 2008
Posts: 33
Location: CHENNAI
|
|
|
|
Hi Sushanth,
Yes i have the control card and load data's for different tables.I just want to create a Jcl in test region using the above control card and data to refresh/load the test environment.
Hi Ekta,Sushant
Can you ppl plz explain me from the below code is 'A51DD2P1.PNASCO.AGENT' a table name.
EXEC SQL
DECLARE C001 CURSOR FOR
SELECT * FROM A51DD2P1.PNASCO.AGENT
ENDEXEC |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Apsar,
Can you show us your code, so far you have prepared ?
| Quote: |
| Can you ppl plz explain me from the below code is 'A51DD2P1.PNASCO.AGENT' a table name. |
This is a 3 part-name,
LOCATIONofPROD.TABLEQUALIFIER.TABLENAME
Sushanth |
|
| Back to top |
|
|
apsar.kogatam
Warnings : 1
New User
Joined: 27 Jun 2008
Posts: 33
Location: CHENNAI
|
|
|
|
Hi Sushant
The below is the Jcl am using to load one table.Similarly i need a jcl to load different tables with diff control cards.I dont want to use the DSNUTILB for all tables as seperate steps in a single job.Please let me know if any alternate way or code is there.
//HSLOAD JOB (U,NM),'UNLOAD.BMC',
// CLASS=A,MSGCLASS=J,
// USER=xxxxxxx
/*ROUTE XEQ DB2H
//****************************************************
//* JS030 - TO LOAD DATA INTO TABLE
//****************************************************
//JS010 EXEC PGM=DSNUTILB,REGION=0M,
// PARM='DB2T,UT943801'
//SYSPRINT DD SYSOUT=*
//UTPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSDISC DD SPACE=(CYL,(200,100)),UNIT=SYSDA
//SYSMDISC DD SYSOUT=*
//SYSERR DD SPACE=(CYL,(200,100)),UNIT=SYSDA
//SYSMAP DD SPACE=(CYL,(200,100)),UNIT=SYSDA
//SORTWK01 DD SPACE=(CYL,(200,100)),UNIT=SYSDA
//SORTWK02 DD SPACE=(CYL,(200,100)),UNIT=SYSDA
//SYSUT1 DD SPACE=(CYL,(500,250)),UNIT=SYSDA
//SORTOUT DD SPACE=(CYL,(500,250)),UNIT=SYSDA
//SYSREC01 DD DISP=SHR,DSN=LOAD DATA
//SYSIN DD DISP=SHR,DSN=CNTRLCARD |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Apsar,
| Quote: |
| i am looking the optimised way to code the jcl instead of having several load steps for more tables. |
You can use JCL PROC & Symoblics.
JCL proc
| Code: |
//JS010 EXEC PGM=DSNUTILB,REGION=0M,
// PARM='DB2T,UT943801'
//SYSPRINT DD SYSOUT=*
//UTPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSDISC DD SPACE=(CYL,(200,100)),UNIT=SYSDA
//SYSMDISC DD SYSOUT=*
//SYSERR DD SPACE=(CYL,(200,100)),UNIT=SYSDA
//SYSMAP DD SPACE=(CYL,(200,100)),UNIT=SYSDA
//SORTWK01 DD SPACE=(CYL,(200,100)),UNIT=SYSDA
//SORTWK02 DD SPACE=(CYL,(200,100)),UNIT=SYSDA
//SYSUT1 DD SPACE=(CYL,(500,250)),UNIT=SYSDA
//SORTOUT DD SPACE=(CYL,(500,250)),UNIT=SYSDA
//SYSREC01 DD DISP=SHR,DSN=&LOADDATA
//SYSIN DD DISP=SHR,DSN=&CNTRLCARD |
You will have to create another jcl which calls the above proc, so this JCL will be calling the same proc.
| Code: |
| //TABLE1 EXEC JS010,&LOADDATA='dsn',CNTRLCARD='table1 dsn' |
With this not tested code, still there wont be any optimization. Only a better view.
Sushanth |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| I dont want to use the DSNUTILB for all tables as seperate steps in a single job. |
Why not?
If separate steps are used, the process will be more easily managed/modified as well as allowing the easy execution of "pieces" when necessary. |
|
| Back to top |
|
|
Ajay Baghel
Active User
.jpg)
Joined: 25 Apr 2007
Posts: 206
Location: Bangalore
|
|
|
|
I have a doubt here. As Apsar told that he already has the unloaded data in file, then why is there any need of cursor in the SYSIN control card (why are we using cursor and not INDD for loading).
Also, i think there would be one SYSREC (unload dataset) for each table unloaded. So there should be multiple SYSREC datasets or multiple steps in JCL
Thank you
Ajay |
|
| Back to top |
|
|
apsar.kogatam
Warnings : 1
New User
Joined: 27 Jun 2008
Posts: 33
Location: CHENNAI
|
|
|
|
Hi Team,
I am trying the below Jcl to load the data into different db2 tables.
I have one dataset which contains control cards for more than 10 tables.And I have load data's seperately for each and every table.below is the sample Jcl.We can have seperate control cards for each and every table to load but please let me know is there any way to use one PS dataset which contains controlcards for many tables to load data.how to differentiate each control card.
//TESTLOAD JOB (U,NM),'TSLOAD',CLASS=J,MSGCLASS=J,
// REGION=0M
//*
//TABLE01 EXEC JS010,&LOADDATA='XXX.XXX.XXX.UNLOAD',
// &CNTRLCARD='ZZZ.ZZZ.ZZZ.CNTRLCARD'
//TABLE02 EXEC JS010,&LOADDATA='YYY.YYY.YYY.UNLOAD',
// &CNTRLCARD='ZZZ.ZZZ.ZZZ.CNTRLCARD'
.
.
.
.
.
.
//TABLE11 EXEC JS010,&LOADDATA='AAA.AAA.AAA.UNLOAD',
// &CNTRLCARD='ZZZ.ZZZ.ZZZ.CNTRLCARD' |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Hi Apsar,
So us your so far done JCL proc, JCL which calls the proc & your CONTROL card.
In my above code, i made a little typo mistake(no ampersand)
| Code: |
| //TABLE1 EXEC JS010,&LOADDATA='dsn',CNTRLCARD='table1 dsn' |
It should be like
| Code: |
| //TABLE1 EXEC JS010,LOADDATA='dsn',CNTRLCARD='table1 dsn' |
And why do want, everything to be in one dataset ?
Sushanth |
|
| Back to top |
|
|
apsar.kogatam
Warnings : 1
New User
Joined: 27 Jun 2008
Posts: 33
Location: CHENNAI
|
|
|
|
Hi Sushant,
The different control cards for diff tables is getting created in one dataset in production and the same is XCOMED to test region.I would like to use the same dataset in test region as i dont want to seperate each and every control card from the existing dataset. |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Hi Apsar,
Can you show us the control card or sample of your control card.
Sushatnth |
|
| Back to top |
|
|
apsar.kogatam
Warnings : 1
New User
Joined: 27 Jun 2008
Posts: 33
Location: CHENNAI
|
|
|
|
LOAD DATA INDDN SYSREC00 LOG NO INTO TABLE
M0C.T535702
(
COLUMN1 POSITION( )
COLUMN2 POSITION( )
COLUMN3 POSITION( )
COLUMN4 POSITION( )
)
LOAD DATA INDDN SYSREC01 LOG NO INTO TABLE
M0C.T535703
(
COLUMN1 POSITION( )
COLUMN2 POSITION( )
COLUMN3 POSITION( )
COLUMN4 POSITION( )
)
LOAD DATA INDDN SYSREC02 LOG NO INTO TABLE
M0C.T535801
(
COLUMN1 POSITION( )
COLUMN2 POSITION( )
COLUMN3 POSITION( )
COLUMN4 POSITION( )
)
;
;
;
;
etc. |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Apsar,
Only one suggestion i can give is to break the job into multiple jobs, so it will load multiple tables at the same time instead of serially, by doing this you can save time.
Sushanth |
|
| Back to top |
|
|
apsar.kogatam
Warnings : 1
New User
Joined: 27 Jun 2008
Posts: 33
Location: CHENNAI
|
|
|
|
| can you plz provide some clear idea on your comment |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Apsar,
For example you are loading 100 tables in one job, so your LOAD control card will be have 100 LOAD utility statements. So your load goes one by one.
By breaking the single job into 4 jobs, so if each job's LOAD control card having 25 tables and all the 4 jobs executed simultanously. You Execution Time reduces.
Sushanth |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10888
Location: italy
|
|
|
|
looks like You do not want to split the load control statements! isn' it??
then You will have to live with what You have got! a single step, the loads serialized, a heck of a time to rewrite the jcl if something goes wrong.
the best solution is ONE TABLE <==> ONE JOB
runs in parallel for many tables, in case of troubles just rerun for the failing table
if You are not satified with the proposal just change consultants |
|
| Back to top |
|
|
apsar.kogatam
Warnings : 1
New User
Joined: 27 Jun 2008
Posts: 33
Location: CHENNAI
|
|
|
|
| ofcourse i dont want to split the control cards.If any alternate way is there plz let me know.if no options available then i will go for splitting control cards. |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10888
Location: italy
|
|
|
|
| Quote: |
| ofcourse i dont want to split the control cards |
stop the condescending attitude, it will take You nowhere
then of course You will have to live with what You have
if You have already made up Your mind why keep bothering for something that does not have a solution |
|
| Back to top |
|
|
apsar.kogatam
Warnings : 1
New User
Joined: 27 Jun 2008
Posts: 33
Location: CHENNAI
|
|
|
|
Hi enrico,
till now i dont know that we dont have a solution for the above i was just trying the alternate ways. |
|
| Back to top |
|
|
|
|
