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: 669
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: 669
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: 669
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 copying data without knowing location arunsoods DFSORT/ICETOOL 6 Thu Jul 20, 2017 1:03 pm
This topic is locked: you cannot edit posts or make replies. Fetching data from BAI File arunsoods JCL & VSAM 1 Wed Jul 19, 2017 4:28 pm
No new posts Loading data to table gives wrong for... Raghu navaikulam DB2 18 Thu Jul 13, 2017 2:11 pm
No new posts SQL - select data available in index Nileshkul DB2 3 Mon Jun 26, 2017 1:30 am
No new posts NDM syntax checking without actually ... GAPX1 All Other Mainframe Topics 0 Wed Jun 07, 2017 2:36 am


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