IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Data in variable length PS file to be loaded into DB2 table
IBM Mainframe Forums -> DB2
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
neelus.s

New User


Joined: 05 Apr 2007
Posts: 4
Location: Minneapolis
PostPosted: Tue May 01, 2007 9:28 am
 Reply with quote
Hi All,

I have a variable length PS file in which data is comma seperated. The data in this file has to be loaded into DB2 table.

Note: Some of the fields in this file are not having data.

Sample file:

00603,PR,Aguadilla,,Agu,18.25460052,-67.09100342
00604,PR,Aguadilla,,Agu,18.25460052,-67.09100342
00605,PR,Aguadilla,,Agu,18.25460052,-67.09100342
00607,PR,Aguas Buenas,,Agu,18.15320015,-66.06099701
00608,PR,Aguirre,,Agu,0,0
00609,PR,Aibonito,,Aib,18.08309937,-66.15499878
00610,PR,Anasco,,Ana,18.1704998,-67.08200073
00611,PR,Angeles,,Ang,18.17130089,-66.47499847
00612,PR,Arecibo,,Are,18.28280067,-66.42500305
00613,PR,Arecibo,,Are,18.28280067,-66.42500305
00615,PR,Arroyo,,Arr,17.58040047,-66.03399658
00616,PR,Bajadero,,Baj,18.25429916,-66.41000366

there are 7 fields in the above file.

I will be using Platinum load JCL or IBM load JCL to load data into the table. Basically i want to format this file before loading the table. Also giving be the table structure below.

ZIP_C CHAR(5)
ST_C CHAR(2)
CITY_N VARCHAR(35)
CNTY_N CHAR(35)
CITY_ABBR_N VARCHAR(3)
LATTD_I FLOAT(8)
LNGTD_I FLOAT(8)

Expecting replies as sonn as possible as i am badly stuck in this problem

Regards,
Neelima
Back to top
View user's profile Send private message
MFRASHEED

Active User


Joined: 14 Jun 2005
Posts: 186
Location: USA
PostPosted: Tue May 01, 2007 9:49 pm
 Reply with quote
If a table has a varchar field definition looks like:

Code:

EXEC SQL DECLARE TESTTBL TABLE                         
( ACCT_ID                        CHAR(19) NOT NULL,     
  MISC_TX                        VARCHAR(320) NOT NULL   
) END-EXEC.                                             


and COBOL DCLGEN will be something like this:

Code:

01  DCLTESTTBL.                                         
    10 ACCT-ID             PIC X(19).               
    10 MISC-TX.                                     
       49 MISC-TX-LEN      PIC S9(4) USAGE COMP.   
       49 MISC-TX-TEXT     PIC X(320).             



where MISC-TX-LEN is length of the variable text.

So you can format PS file in same format as DCLGEN of table. And use LOAD statement to load data. Note even though table has variable length fields, there is a maximum length of field. What this means is in load file there will be place holder for whole 320 bytes but there may be data in 320 bytes as defined by LEN parameter. And load file will be Fixed Block where total length can be derived from cobol DCLGEN structure.

Hope this helps.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 

Similar Topics
Topic Forum Replies
No new posts How to save SYSLOG as text data via P... All Other Mainframe Topics 1
No new posts Compare 2 files and retrive records f... DFSORT/ICETOOL 3
No new posts FTP VB File from Mainframe retaining ... JCL & VSAM 8
No new posts Store the data for fixed length COBOL Programming 1
No new posts Extract the file name from another fi... DFSORT/ICETOOL 6
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us