View previous topic :: View next topic
|
Author |
Message |
Sumeru
New User
Joined: 15 Apr 2006 Posts: 10
|
|
|
|
Hi.
I am not proficient in DFSORT and need your help.I have a file of 78 fields of which the last 9(70-79) are in PD format.
Here it is.
Code: |
6----+----7----+----8
*********************
B109000020......ý64
G109000020......ð64 |
Now the issue I have is
1) How to declare a table in DB2 to load the data in the file. What format will the datatype takes to accommodate the PD and of how much length.
2) How to convert the PD to numeric or char and output the same into another file in readable format.
3)What is the solution for the above 2 questions if it is a ZD instead of PD.
Thanks in advance. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello Sumeru and welcome to the forums,
Please notice that your example data has been "Code"ed (using the tag near the top of the reply panel). If you "Code" your jcl, control statements, and data, it will use a fixed font rather than a proportional font and will be much more readable.
Quote: |
I have a file of 78 fields of which the last 9(70-79) are in PD format |
The sample data does not match this. Also, i believe you meant the lrecl not the number of fields.
Tables are usually defined before code is written. I'd check with your project lead or dba to learn how this table is defined (or will be).
Packed-decimal is a readable format |
|
Back to top |
|
|
Sumeru
New User
Joined: 15 Apr 2006 Posts: 10
|
|
|
|
Hi Dick,
Thanks for the quick reply. The situation is like this. There is some old data needed by the customer and some tapes were identified for that. I pulled the data from the tape as-is and now I have to create a table for that and load it into that. The only extra info I have is the following DDL for the table.
Code: |
INPUT @002 MONTH $CHAR02.
@004 SRC $CHAR03.
@007 REGION $CHAR01.
@008 INDEX $CHAR05.
@026 EMPSER $CHAR06.
@061 ACCT $CHAR09.
@061 DIV $CHAR02.
@063 MAJ $CHAR03.
@066 MINOR $CHAR04.
@070 AMT PD7.2; |
I could identify all the other fields except the PD7.2 . There is one more table with ZD also defined in it. I never worked on this stuff before and loading into the table is giving me conversion errors for the AMT field.
I meant LRECL only ..not fields. The LRECL for the file is 78 of which the last 9 is Packed Decimal.
Please advice and let me know if any further info is needed. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Please re-post your sample data (and this time, please use "hex on" as well as the "Code" tag).
Quote: |
The LRECL for the file is 78 of which the last 9 is Packed Decimal. |
In the sample data, the "last 9" do not appear to be packed decimal. A packed-decimal number will not end with a visable digit due to the way packed-decimal data is stored. |
|
Back to top |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
|
|
Back to top |
|
|
Sumeru
New User
Joined: 15 Apr 2006 Posts: 10
|
|
|
|
Hi, Please find the data below.
Code: |
Command ===> Scroll ===> CSR
----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8
********************************* Top of Data **********************************
5010288AUTCL 62B109000020......ý64
5010288AUTCL 62G109000020......ð64
5010288AUTCL 62B109000020.....Ä.64
5010288AUTCL 62H109000020.....Ä.64
5010288AUTCL 62G109009799......ý64
5010288AUTCL 100210996......ð64
5010288AUTCL 62H109009799.....Ä.64 |
With Hex on the code looks as follows.
Code: |
BROWSE SRC02.IRSTAPES.HQ.IRS1995.JAN.OUT100 Line 00000000 Col 001 080
Command ===> Scroll ===> CSR
********************************* Top of Data **********************************
------------------------------------------------------------------------------
5010288AUTCL 62B109000020......ý64
FFFFFFFCEECD444444444444444444444444444444444444444444444FFCFFFFFFFFF0000028FF44
501028814333000000000000000000000000000000000000000000000622109000020000005D6400
------------------------------------------------------------------------------
5010288AUTCL 62G109000020......ð64
FFFFFFFCEECD444444444444444444444444444444444444444444444FFCFFFFFFFFF0000028FF44
501028814333000000000000000000000000000000000000000000000627109000020000005C6400
------------------------------------------------------------------------------
5010288AUTCL 62B109000020.....Ä.64
FFFFFFFCEECD444444444444444444444444444444444444444444444FFCFFFFFFFFF0000060FF44
501028814333000000000000000000000000000000000000000000000622109000020000073D6400 |
Please ask me if further info is needed. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello Sumeru,
You have a packed-decimal value in pos 70-76. The next 2 bytes ('64') are not packed nor are they part of a packed-decimal value)
Now that the packed-decimal "field" has been identified, what do you want to do with it? As is, it is valid as input to a mainframe process (cobol, easytrieve, etc).
Please show the output you want from this input. |
|
Back to top |
|
|
Sumeru
New User
Joined: 15 Apr 2006 Posts: 10
|
|
|
|
Hi Dick,
I need to load the above data to a table which I have to create.I got the ddl for all fields except the last one which takes the Packed Decimal.
I want to know how I should declare the field with how much length.
Instead of that I can convert the PD to decimal using SORT and then load the decimal into a table.
I also have to create two more tables with the PD and ZD.
like this
Code: |
@111 PISRC $CHAR02.
@113 HOURS ZD6.1
@119 BURDAMT PD6.2
@125 BENEFIT $CHAR02.
|
If you can give me the solution on how to declare the table DDL with respect to the length of the PD and ZD field or how to convert the fields to normal decimal data using SORT then it will solve my problem.I was supposed to give the data yesterday but conveyed the problem to the customer, so I am cool for some time.
Thanks |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
On most systems, table definitions are the responsibility of the database support people. I suggest you talk with your dba(s) and ask them to provide the table definition including the column names and attributes for the data to be loaded.
You should not need to reformat the data you have in either case in order to load it into the tables. |
|
Back to top |
|
|
|