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
 

 

How to load CLOB data into table?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
vimalravi83

New User


Joined: 28 Feb 2010
Posts: 21
Location: Bloomington, IL

PostPosted: Tue Mar 23, 2010 1:17 am    Post subject: How to load CLOB data into table?
Reply with quote

Using the below card, I could successfully unload data from a table into a dataset;
//STEP20 EXEC DSNTSOP,RC=4
//SYSTSIN DD *
DSN SYSTEM(HD0D) RETRY(0) TEST(0)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) -
PARMS('SQL,LOBFILE(ABCD)')
END
/*

The unloaded data is in SYSREC card of the JCL. Now I am trying to load this data back to table using the same SYSREC dataset. Anyone knows, how can I write the LOAD card for this. I do not want to use LOAD REPLACE which wipes out all data in the table. I would like to use something like LOAD RESUME which add the new rows.
icon_sad.gif
Back to top
View user's profile Send private message

daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 646
Location: Pennsylvania

PostPosted: Tue Mar 23, 2010 1:56 am    Post subject:
Reply with quote

Since I can not see your Proc DSNTSOP, I am using my special glasses.

Your Load Cards may be written to //SYSPUNCH DD.

If you have that in your Proc, try writing it to a dataset (lrecl=80).

You can edit the cards to be Resume Yes if needed.
Back to top
View user's profile Send private message
vimalravi83

New User


Joined: 28 Feb 2010
Posts: 21
Location: Bloomington, IL

PostPosted: Tue Mar 23, 2010 3:17 am    Post subject: Reply to: How to load CLOB data into table?
Reply with quote

Yes. I have the LOAD card in the SYSPUNCH of the job. I tried using DSNUPROC utility (IBM provided utility) with the same LOAD card, to load the rows back into the table, but the job failed. It is not recognizing the load card i provided. It gave the following error in job output. Not sure what it is;

DSNURELD - A REQUIRED DD CARD OR TEMPLATE IS MISSING. NAME=SYSUT1
DSNURELD - A REQUIRED DD CARD OR TEMPLATE IS MISSING. NAME=SORTOUT
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: Tue Mar 23, 2010 4:20 am    Post subject:
Reply with quote

Hello,

For someone to help, you need to post the complete jobstream (jcl and control ststements and any diagnostic info generated by the run).
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 646
Location: Pennsylvania

PostPosted: Tue Mar 23, 2010 7:53 pm    Post subject:
Reply with quote

Not only do I concur with Dick that a complete jobstream is required, which I aluded to in my post (most have been to subtle).

I also think the error messages are VERY CLEAR as to where you should start looking. It tells what dd names it is looking for.
Back to top
View user's profile Send private message
vimalravi83

New User


Joined: 28 Feb 2010
Posts: 21
Location: Bloomington, IL

PostPosted: Tue Mar 23, 2010 8:01 pm    Post subject: Reply to: How to load CLOB data into table?
Reply with quote

I tried added those datasets, but the JCL died again. Here is the full JCL I used ;
STEP10 EXEC DSNUPROC,
// SYSTEM='HD0D',
// UID='JobSTEP10',
// UTPROC='',
// RC='4'
//SYSPRINT DD SYSOUT=*
//SORTWK01 DD DISP=(NEW,DELETE,DELETE),
// SPACE=(TRK,(1,1),RLSE)
//SYSUT1 DD DISP=(NEW,DELETE,DELETE),
// SPACE=(TRK,(1,1),RLSE)
//SORTOUT DD DISP=(NEW,DELETE,DELETE),
// SPACE=(TRK,(1,1),RLSE)
//SYSERR DD DISP=(NEW,DELETE,DELETE),
// SPACE=(TRK,(1,1),RLSE)
//SYSMAP DD DSN=&&SYSMAP,DISP=(MOD,DELETE,CATLG),
// SPACE=(TRK,(1,1),RLSE)
//SYSREC DD DSN=input file,DISP=SHR
//SYSIN DD *
LOAD DATA
RESUME YES
INTO TABLE EMP_TABLE
(
RES_ID POSITION( 1 )
INTEGER ,
RESUME POSITION( 5 )
CHAR( 31) CLOBF
)
/*

The below message is there in the joblog. Not sure it indicates an error.

DSNUGSDA - RECFM=U FOR DDNAME SYSMAP
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 646
Location: Pennsylvania

PostPosted: Tue Mar 23, 2010 8:11 pm    Post subject:
Reply with quote

Quote:

Not sure it indicates an error.


Did the Job Load the Data into you Table?

If not, perhaps you need a record format (I do not in my JCL), but it is worth a try....

DCB=(RECFM=FB)
Back to top
View user's profile Send private message
vimalravi83

New User


Joined: 28 Feb 2010
Posts: 21
Location: Bloomington, IL

PostPosted: Wed Mar 24, 2010 12:57 am    Post subject: Reply to: How to load CLOB data into table?
Reply with quote

That did the trick ! I added RECFM=FB and job ran successfully. It inserted records containing LOB data into the table. Thanks a lot Dave ! icon_razz.gif

Below is the modification i made;
//SYSMAP DD DSN=&&SYSMAP,DISP=(MOD,DELETE,CATLG),
// DCB=(RECFM=FB),
// SPACE=(TRK,(1,1),RLSE)
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 Mar 24, 2010 1:31 am    Post subject:
Reply with quote

Hi Vimal,

You should be aware that the jcl has a problem. . .

&&SYSMAP is a temporary dsn and catlg won't work. . . (Unless i missed some memo).
Back to top
View user's profile Send private message
vimalravi83

New User


Joined: 28 Feb 2010
Posts: 21
Location: Bloomington, IL

PostPosted: Wed Mar 24, 2010 2:28 am    Post subject: Reply to: How to load CLOB data into table?
Reply with quote

That's correct. I got that from another JCL. Even though temp dataset don't have existance outside the job, I will change it to something like(MOD,DELETE,DELETE) which looks more good. Thanks for notifying !
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 Mar 24, 2010 2:42 am    Post subject: Reply to: How to load CLOB data into table?
Reply with quote

You're welcome - good luck icon_smile.gif

d
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 -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Storing huge volume of data, compare ... Pradeep K M All Other Mainframe Topics 3 Mon Jan 16, 2017 5:08 pm
No new posts how to recover an uncataloged VSAM da... archanamuthukrishnan All Other Mainframe Topics 3 Wed Jan 11, 2017 6:18 pm
No new posts HALDB data refresh/copy from producti... vineetanand2007 IMS DB/DC 1 Mon Jan 02, 2017 11:16 am
No new posts JES2 JEC: Use UNIX Pipes to Pass Data... Virendra Shambharkar JCL & VSAM 21 Tue Dec 20, 2016 6:55 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am


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