| View previous topic :: View next topic |
| Author |
Message |
pradeepg1
Beginner
Joined: 06 Jan 2003
Posts: 20
Topics: 7
Location: Columbus, OH
|
 Posted: Fri Jan 24, 2003 10:15 am Post subject: Comma seperated input file for LOAD utility |
 |
|
Hi,
Can I use the file which has the fields (data) with comma seperated as input instead of fixed length data file for loading by using LOAD utility.
Thanks.
Pradeep |
|
| Back to top |
|
 |
pradeepg1
Beginner
Joined: 06 Jan 2003
Posts: 20
Topics: 7
Location: Columbus, OH
|
| Posted: Fri Jan 24, 2003 11:56 am Post subject: |
|
|
Hi Everyone,
Let me re-phrase my question.
Is there any opiton available on LOAD utility through which I can tell the utility that each filed is separated by a comma in input file (INDDN).
Or is there any other method (tool) available to load comma separated file into DB2 tables.
Thanks,
Pradeep |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Fri Jan 24, 2003 1:06 pm Post subject: |
|
|
Pradeep,
you can specify the relative positions of the columns in the file in the load card and load it. The following load card will load the comma seperated file into a db2 table.
| Code: |
LOAD DATA REPLACE LOG NO INDDN SYSREC00
INTO TABLE TABLE_NAME
( COL1 POSITION (1:3) CHAR(3),
COL2 POSITION (5:39) CHAR(36),
COL3 POSITION (41:45) CHAR(6),
COL4 POSITION (47:48) CHAR(3),
COL5 POSITION (50:64) CHAR(16) )
|
see col2 actually starts in postion 4 in the table but in the file it starts at 5 since col1 is delimited by a comma in the 4th byte.so in the load card we specify the position of col2 in the file and the length and type of the column.
But it is pain to create the load cards as you need to hard code the positions.
There is another way round to get the load cards but will still need a manual intervention
unload the table using the following JCL.
| 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 if you look at the syspunch output it will generate the load card as follows
| Code: |
LOAD DATA REPLACE LOG NO INDDN SYSREC00
INTO TABLE TABLE_NAME
( COL1 POSITION (1:3) CHAR(3),
COL2 POSITION (4:39) CHAR(36),
COL3 POSITION (40:45) CHAR(6),
COL4 POSITION (46:48) CHAR(3),
COL5 POSITION (49:64) CHAR(16) )
|
For this load card you need to change the relative positions like the load card shown above.
Hope this helps...
cheers
kolusu |
|
| Back to top |
|
|
pradeepg1
Beginner
Joined: 06 Jan 2003
Posts: 20
Topics: 7
Location: Columbus, OH
|
| Posted: Fri Jan 24, 2003 1:36 pm Post subject: |
|
|
Hi Kolusu,
My data fields in the input file always won't start at the same position.
We are getting this data file from C/S environment. In the following example NAME field wouldn't always start at 6th position becuase
empl number may be 4 digits some times and 5 digits some times.
Same case with JOB field.
Table Desc:
========
EMP-Num SMALLINT
NAME CHAR(30)
JOB CHAR(20)
Sample data in input file
----------------------------
1111,JOHN Q,Manager
1234,Scott Francis,Programmer
112233,Robert Gibbs,Analyst
Thanks,
Pradeep |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Fri Jan 24, 2003 2:55 pm Post subject: |
|
|
Pradeep,
As far as I know there is no option in DB2 to load where the position of the data is variable.So you need to reformat your input files to the column positions and then load it to the DB2 tables.
Kolusu |
|
| Back to top |
|
|
pradeepg1
Beginner
Joined: 06 Jan 2003
Posts: 20
Topics: 7
Location: Columbus, OH
|
| Posted: Fri Jan 24, 2003 4:12 pm Post subject: |
|
|
Not a happy news, this is forcing me to write a cobol pgm to format the data.
Thanks again Kolusu.
- Pradeep |
|
| Back to top |
|
|
zatlas
Beginner
Joined: 17 Dec 2002
Posts: 43
Topics: 4
|
| Posted: Sun Jan 26, 2003 10:19 am Post subject: |
|
|
Hi pradeepg1
| Quote: | | Not a happy news, this is forcing me to write a cobol pgm to format the data. |
While at it, since this is a long time problem in the COBOL/Mainframe arena, you may consider writing a full scale COBOL utility that would accept a comma (or anything else) seperated file (including the ability to use quote or double quote or something else as text qualifier) and the ability to designate the first row as field names and the ability to declare the output file structure... and if you post it here or on the WWW.CBTTAPE.ORG ,I promise it will be widely used.
I thought about one but never got around to do it 
ZA |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
