IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Jcl to load the data in to different db2 tables


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
apsar.kogatam
Warnings : 1

New User


Joined: 27 Jun 2008
Posts: 33
Location: CHENNAI

PostPosted: Tue Jan 12, 2010 5:11 pm
Reply with quote

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
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1207
Location: Bangalore,India

PostPosted: Tue Jan 12, 2010 5:31 pm
Reply with quote

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
View user's profile Send private message
apsar.kogatam
Warnings : 1

New User


Joined: 27 Jun 2008
Posts: 33
Location: CHENNAI

PostPosted: Tue Jan 12, 2010 5:34 pm
Reply with quote

Hi Ekta,

Can you please explain what is crossload and how to use it with Jcl.
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1207
Location: Bangalore,India

PostPosted: Tue Jan 12, 2010 5:41 pm
Reply with quote

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
View user's profile Send private message
apsar.kogatam
Warnings : 1

New User


Joined: 27 Jun 2008
Posts: 33
Location: CHENNAI

PostPosted: Tue Jan 12, 2010 5:50 pm
Reply with quote

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
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1018
Location: India

PostPosted: Tue Jan 12, 2010 6:18 pm
Reply with quote

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
View user's profile Send private message
apsar.kogatam
Warnings : 1

New User


Joined: 27 Jun 2008
Posts: 33
Location: CHENNAI

PostPosted: Wed Jan 13, 2010 11:01 am
Reply with quote

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
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1018
Location: India

PostPosted: Wed Jan 13, 2010 11:35 am
Reply with quote

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
View user's profile Send private message
apsar.kogatam
Warnings : 1

New User


Joined: 27 Jun 2008
Posts: 33
Location: CHENNAI

PostPosted: Wed Jan 13, 2010 4:46 pm
Reply with quote

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
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1018
Location: India

PostPosted: Wed Jan 13, 2010 7:20 pm
Reply with quote

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
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Wed Jan 13, 2010 9:50 pm
Reply with quote

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
View user's profile Send private message
Ajay Baghel

Active User


Joined: 25 Apr 2007
Posts: 206
Location: Bangalore

PostPosted: Thu Jan 14, 2010 10:50 am
Reply with quote

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
View user's profile Send private message
apsar.kogatam
Warnings : 1

New User


Joined: 27 Jun 2008
Posts: 33
Location: CHENNAI

PostPosted: Tue Jan 19, 2010 2:34 pm
Reply with quote

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
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1018
Location: India

PostPosted: Tue Jan 19, 2010 5:28 pm
Reply with quote

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
View user's profile Send private message
apsar.kogatam
Warnings : 1

New User


Joined: 27 Jun 2008
Posts: 33
Location: CHENNAI

PostPosted: Tue Jan 19, 2010 5:33 pm
Reply with quote

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
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1018
Location: India

PostPosted: Tue Jan 19, 2010 6:10 pm
Reply with quote

Hi Apsar,

Can you show us the control card or sample of your control card.

Sushatnth
Back to top
View user's profile Send private message
apsar.kogatam
Warnings : 1

New User


Joined: 27 Jun 2008
Posts: 33
Location: CHENNAI

PostPosted: Wed Jan 20, 2010 2:00 pm
Reply with quote

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
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1018
Location: India

PostPosted: Wed Jan 20, 2010 2:41 pm
Reply with quote

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
View user's profile Send private message
apsar.kogatam
Warnings : 1

New User


Joined: 27 Jun 2008
Posts: 33
Location: CHENNAI

PostPosted: Wed Jan 20, 2010 2:44 pm
Reply with quote

can you plz provide some clear idea on your comment
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1018
Location: India

PostPosted: Wed Jan 20, 2010 2:49 pm
Reply with quote

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
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10833
Location: italy

PostPosted: Wed Jan 20, 2010 2:52 pm
Reply with quote

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
View user's profile Send private message
apsar.kogatam
Warnings : 1

New User


Joined: 27 Jun 2008
Posts: 33
Location: CHENNAI

PostPosted: Wed Jan 20, 2010 3:00 pm
Reply with quote

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
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10833
Location: italy

PostPosted: Wed Jan 20, 2010 3:03 pm
Reply with quote

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
View user's profile Send private message
apsar.kogatam
Warnings : 1

New User


Joined: 27 Jun 2008
Posts: 33
Location: CHENNAI

PostPosted: Wed Jan 20, 2010 3:15 pm
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Split a record with data in a differe... DFSORT/ICETOOL 8
No new posts data moved using MVI on para-name. PL/I & Assembler 4
No new posts Setting CA7 user variable from data s... CA Products 7
No new posts pass data as symbolic parameter from ... CLIST & REXX 2
No new posts How to display the leading zeros of a... DB2 7
Search our Forums:

Back to Top