Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Jcl to load the data in to different db2 tables
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Jcl to load the data in to different db2 tables
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: 1187
Location: Bangalore,India

PostPosted: Tue Jan 12, 2010 5:31 pm    Post subject:
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    Post subject: Reply to: Jcl to load the data in to different db2 tables
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: 1187
Location: Bangalore,India

PostPosted: Tue Jan 12, 2010 5:41 pm    Post subject:
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    Post subject: Reply to: Jcl to load the data in to different db2 tables
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: 1013
Location: India

PostPosted: Tue Jan 12, 2010 6:18 pm    Post subject:
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    Post subject: Reply to: Jcl to load the data in to different db2 tables
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: 1013
Location: India

PostPosted: Wed Jan 13, 2010 11:35 am    Post subject:
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    Post subject: Reply to: Jcl to load the data in to different db2 tables
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: 1013
Location: India

PostPosted: Wed Jan 13, 2010 7:20 pm    Post subject:
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

Site Director


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

PostPosted: Wed Jan 13, 2010 9:50 pm    Post subject:
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: 205
Location: Bangalore

PostPosted: Thu Jan 14, 2010 10:50 am    Post subject:
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    Post subject: Reply to: Jcl to load the data in to different db2 tables
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: 1013
Location: India

PostPosted: Tue Jan 19, 2010 5:28 pm    Post subject:
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    Post subject: Reply to: Jcl to load the data in to different db2 tables
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: 1013
Location: India

PostPosted: Tue Jan 19, 2010 6:10 pm    Post subject:
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    Post subject: Reply to: Jcl to load the data in to different db2 tables
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: 1013
Location: India

PostPosted: Wed Jan 20, 2010 2:41 pm    Post subject:
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    Post subject: Reply to: Jcl to load the data in to different db2 tables
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: 1013
Location: India

PostPosted: Wed Jan 20, 2010 2:49 pm    Post subject:
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
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Is the 'prompt' data for the DSLIST p... Willy Jensen TSO/ISPF 2 Tue Dec 06, 2016 4:38 am
This topic is locked: you cannot edit posts or make replies. How to move a long alphanumeric data ... lind sh COBOL Programming 8 Mon Dec 05, 2016 7:51 pm
No new posts ODPP(Optim Data privacy Provider) Iss... Rama kishore IBM Tools 1 Mon Nov 07, 2016 5:46 pm
No new posts Can sending 5 MB data between cobol p... Kevin Vaz CICS 12 Tue Oct 18, 2016 4:50 pm
No new posts output data in CSV Format janmejay COBOL Programming 8 Sat Oct 15, 2016 2:20 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us