MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Load varchar data problem

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
bindu
Beginner


Joined: 05 Jan 2003
Posts: 17
Topics: 7
PostPosted: Tue Jan 07, 2003 10:41 pm    Post subject: Load varchar data problem Reply with quote
Hello,
I have a data in a sequential file.This file is a simple log file and records can span upto 300 characters in length. To upload this data into db2 tables, i followed the following procedure:
Created a control card as follows:
LOAD DATA RESUME YES LOG NO INDDN SYSREC00
INTO TABLE username.aaaaa_TABLE
(
aaaaa POSITION( 1 )
VARCHAR
)

Then I created a table under my user profile as follows:
Create Table aaaaa_TABLE (
aaaaa VARCHAR (300)
)in dsqdbdef.dsqtsdef;

Then i submitted the JCL for uploading to the db2 tables:
It gave the following error message:
DBU1- DSNURWRF - RECORD (1) WILL BE DISCARDED DUE TO INVALID 'aaaaa' COLUMN SPECIFICATION

But the loading was working fine with all other data types
Can you please help me in solving this?
Is this the problem with the control card? I'm not able to trouble shoot where the error is .
Back to top
View user's profile Send private message Send e-mail
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
PostPosted: Tue Jan 07, 2003 10:52 pm    Post subject: Reply with quote
Bindu,

Since the column is defined as varchar you need to consider the 2 bytes the length field. change your load card to the following and see if it works

Code:

LOAD DATA LOG NO INDDN SYSREC00 INTO TABLE                         
    username.aaaaa_TABLE                                                     
 (                                                                 
 COLNAME                                    POSITION(       1         )
 VARCHAR                                                           
      NULLIF(     303)='?'                                         
 )


This job also takes care if the column is defined to have nulls.

Hope this helps...

cheers

kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
PostPosted: Wed Jan 08, 2003 5:37 pm    Post subject: Reply with quote
Bindu,

I don't think you can load a sequential file directly to the varchar column. Basically the varchar column consists of 2 parts.The first part is the length and the second part is the text.

Code:


01  DCLBIN.                                       
    10 COL.                                       
       49 COL-LEN           PIC S9(4) USAGE COMP. 
       49 COL-TEXT          PIC X(300).           


So your input file should have the length in binary format.

To explain this clearly ,insert a record into the table via spufi.spufi takes cares of the length part automatically when inserting.

Code:

INSERT INTO TABLENAME VALUES('AAAA');


now use the following jcl to unload the table
Code:

//STEP0100 EXEC PGM=IKJEFT01                 
//*                                         
//SYSTSPRT DD  SYSOUT=*,DCB=BLKSIZE=121     
//SYSPRINT DD  SYSOUT=*                     
//SYSTSIN  DD  *                             
 DSN SYSTEM(XXXX)                           
 RUN  PROGRAM(DSNTIAUL) -                   
      PLAN(DSNTIAUL)    -                   
      PARMS('SQL')      -                   
      LIB('XXXX.RUNLIB.LOAD')               
//SYSREC00 DD DSN=YOUR UNLOAD FILE,       
//            DISP=(NEW,CATLG,DELETE),       
//            UNIT=SYSDA,                     
//            SPACE=CYL,(1,1),RLSE)                 
//SYSPUNCH DD SYSOUT=*                       
//SYSIN    DD  *                             
SELECT *                                     
     FROM                                   
     TABLE NAME                             
             ;                               
/*


Take a look at the output from the job. The SYSPUNCH will have the load card for the table.

Now take a look at the unloaded file.if you are browsing it you will see that
the first 2 bytes will be ..

Now turn "hex on" and you can see the length of the string in hex.It will be as
Code:

00
04


So you need to create your file similar to the unload file to load.

Hope this helps...

cheers

kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
bindu
Beginner


Joined: 05 Jan 2003
Posts: 17
Topics: 7
PostPosted: Wed Jan 08, 2003 9:12 pm    Post subject: Reply with quote
Thanks Kolusu for your valuable suggestions .. I will try the method suggested and get back to you ...
Thanking you once again
Bindu
Back to top
View user's profile Send private message Send e-mail
bindu
Beginner


Joined: 05 Jan 2003
Posts: 17
Topics: 7
PostPosted: Mon Jan 13, 2003 5:05 am    Post subject: Reply with quote
Hello Kolusu,
I tried unloadind the structure from the DB2 table and then load it .But even then it was giving the same error Sad ...Any idea why this is happening Sad(
Back to top
View user's profile Send private message Send e-mail
bindu
Beginner


Joined: 05 Jan 2003
Posts: 17
Topics: 7
PostPosted: Mon Jan 13, 2003 7:24 am    Post subject: Reply with quote
Hello Kolusu,
Got the data loaded into DB2 ..thanks for ur help ..infact the error which we got even after unloading the structure directly from the DB2 tables was due to not allocating 2 spaces for the length field .. So we need to change the control card manually for allocating spaces for the length field.What we unloaded from the DB2 tables were as follows:
LOAD DATA RESUME YES LOG NO NOCOPYPEND INDDN SYSREC00 INTO TABLE
xxxxx.aaaaaa_TABLE
(
aaaaaa POSITION( 1 )
VARCHAR
NULLIF(303)='?'
)

And our input data in the seq. file was as follows:
=COLS> ----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
000100 TIMESTAMP 0 19991115122812
000200 PROCESSOR 0 000 1
000300 FILEID 0 001 000000 023 WCTGEN/QCLSRC(CUCWCF45) 19991115122752 0
000400 FILEEND 0 001 000000

Note: it started from col:2
But when we changed the col of input data starting position to 3 it worked.

But the sad part of this is that we need to do it for each line of input data if it is Varchar ..Am i correct in my understanding?
Sad

Thanks a lot for ur invaluable help

Regards,
Bindu
Back to top
View user's profile Send private message Send e-mail
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
PostPosted: Mon Jan 13, 2003 10:07 am    Post subject: Reply with quote
Bindu,

Just a couple of clarifications.What is your DB2 Version? Did you mention the DCB parameter when unloading the table?? i.e did you mention the LRECL to be 300?

Unload utilities and sort products do not need hard coded DCB parameters as they are capable of calcuating the dcb parms from the sysin cards itself.

I tried to unload and load the data back and every thing went fine. Try using this jcl for unloading and loading the table.

Here XXXX is your DB2 region. Make sure that you don't have the DCB parm coded for unload step.

Code:

//STEP0100 EXEC PGM=IKJEFT01                 
//*                                         
//SYSTSPRT DD  SYSOUT=*,DCB=BLKSIZE=121     
//SYSPRINT DD  SYSOUT=*                     
//SYSTSIN  DD  *                             
DSN SYSTEM(XXXX)                           
RUN  PROGRAM(DSNTIAUL) -                   
      PLAN(DSNTIAUL)    -                   
      PARMS('SQL')      -                   
      LIB('XXXX.RUNLIB.LOAD')               
//SYSREC00 DD DSN=YOUR UNLOAD FILE,       
//            DISP=(NEW,CATLG,DELETE),       
//            UNIT=SYSDA,                     
//            SPACE=CYL,(1,1),RLSE)                 
//SYSPUNCH DD SYSOUT=*                       
//SYSIN    DD  *                             
SELECT *                                     
     FROM                                   
     TABLE NAME                             
             ;                               
/*


Now use the following JCL for loading the table.

Code:

//STEP0100 EXEC PGM=IEFBR14                                             
//*                                                                     
//SYSUT1   DD  DSN=TID.LOAD.SYSDISC,                               
//             DISP=(MOD,DELETE,DELETE),                               
//             SPACE=(CYL,(1,1),RLSE),                                 
//             RECFM=FB                                                 
//*                                                                     
//STEP0200 EXEC PGM=DSNUTILB,           
//         PARM=(XXXX,MYLOAD,'')       
//*                                     
//SYSPRINT DD  SYSOUT=*                 
//UTPRINT  DD  SYSOUT=*                 
//SYSUDUMP DD  SYSOUT=*                 
//SORTOUT  DD  DSN=TID.LOAD.SORTOUT,
//             DISP=(MOD,DELETE,CATLG),
//             UNIT=DISK,               
//             SPACE=(CYL,(2,7),RLSE), 
//             RECFM=FB                 
//SYSDISC  DD  DSN=TID.LOAD.SYSDISC,
//             DISP=(MOD,CATLG,CATLG), 
//             UNIT=DISK,               
//             SPACE=(CYL,(1,1),RLSE), 
//             RECFM=FB                 
//SYSERR   DD  DSN=TID.LOAD.SYSERR,
//             DISP=(MOD,DELETE,CATLG),
//             UNIT=DISK,               
//             SPACE=(CYL,(1,1),RLSE), 
//             RECFM=FB                 
//SYSMAP   DD  DSN=TID.LOAD.SYSMAP,
//             DISP=(MOD,DELETE,CATLG),
//             UNIT=DISK,               
//             SPACE=(CYL,(5,2),RLSE), 
//             RECFM=FB                 
//SYSUT1   DD  DSN=TID.LOAD.SYSUT1,
//             DISP=(MOD,DELETE,CATLG),
//             UNIT=DISK,               
//             SPACE=(CYL,(10,5),RLSE),
//             RECFM=FB                 
//SYSREC00 DD  DSN=YOUR UNLOAD FILE, 
//             DISP=SHR           
 LOAD DATA REPLACE LOG YES INDDN SYSREC00 INTO TABLE               
     TABLE NAME                                                   
  (                                                                 
  AAAAA                                    POSITION(       1         )
  VARCHAR                                                           
       NULLIF(     303)='?'                                         
  )                                                                   
 /*


I used the option REPLACE LOG YES to avoid the copypending status after the load.

Hope this helps...

cheers

kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group