How to load CLOB data into table?

vimalravi83 · Posted: Tue Mar 23, 2010 1:17 am

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.

daveporcelan · Posted: Tue Mar 23, 2010 1:56 am

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.

vimalravi83 · Posted: Tue Mar 23, 2010 3:17 am

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

dick scherrer · Posted: Tue Mar 23, 2010 4:20 am

Hello,

For someone to help, you need to post the complete jobstream (jcl and control ststements and any diagnostic info generated by the run).

daveporcelan · Posted: Tue Mar 23, 2010 7:53 pm

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.

vimalravi83 · Posted: Tue Mar 23, 2010 8:01 pm

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

daveporcelan · Posted: Tue Mar 23, 2010 8:11 pm

vimalravi83 · Posted: Wed Mar 24, 2010 12:57 am

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 !

Below is the modification i made;
//SYSMAP DD DSN=&&SYSMAP,DISP=(MOD,DELETE,CATLG),
// DCB=(RECFM=FB),
// SPACE=(TRK,(1,1),RLSE)

dick scherrer · Posted: Wed Mar 24, 2010 1:31 am

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).

vimalravi83 · Posted: Wed Mar 24, 2010 2:28 am

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 !

dick scherrer · Posted: Wed Mar 24, 2010 2:42 am

You're welcome - good luck

d