|
View previous topic :: View next topic
|
| Author |
Message |
nartcr
New User
Joined: 06 Jun 2007
Posts: 83
Location: anada
|
|
|
|
I have the following table layout from DSNTIAUL:
| Code: |
LOAD DATA LOG NO INDDN SYSREC00 INTO TABLE
TBLNAME
(
TEST__ID POSITION( 1 )
INTEGER ,
TEST_SID POSITION( 5 )
SMALLINT ,
TEST_DTE POSITION( 7 )
DATE EXTERNAL( 10) ,
TEST_TME POSITION( 17 )
TIME EXTERNAL( 8) ,
TEST_FORM_ID POSITION( 25 )
INTEGER ,
TEST_USR_ID POSITION( 29 )
INTEGER
) |
What I am doing is, to sequence the SID number after unloading the table in a flat file.
| Code: |
//SYSIN DD *
SORT FIELDS=(1,4,FI,A,5,2,FI,A)
OUTREC IFTHEN=(WHEN=INIT,
OVERLAY=(5:SEQNUM,2,BI,RESTART=(1,4)))
OUTFIL BUILD=(1,80)
/* |
Apparently, this code wouldn't work, because format FI ( equivalent of smallint ) is not supported in SEQNUM field. Do you have any suggestion on how do I get around this problem? We are using SYNCSORT 2.1.0
After I sequence the SID, I would be reloading the data back to the table. |
|
| Back to top |
|
 |
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
What do you mean by "apparently, this code wouldn't work"?
Do you have more than a possible 32767 TEST_SID per TEST__ID? If not, what do you feel would be the difference between FI and BI, and if so, how would a signed "sequence number" help? |
|
| Back to top |
|
|
nartcr
New User
Joined: 06 Jun 2007
Posts: 83
Location: anada
|
|
|
|
| Bill Woodger wrote: |
What do you mean by "apparently, this code wouldn't work"?
Do you have more than a possible 32767 TEST_SID per TEST__ID? If not, what do you feel would be the difference between FI and BI, and if so, how would a signed "sequence number" help? |
Sorry for the confusion. I am attaching the code,which i submitted. Ignore the code shown in previous post.
| Code: |
//SYSIN DD *
SORT FIELDS=(1,4,FI,A,5,2,FI,A)
OUTREC IFTHEN=(WHEN=INIT,
OVERLAY=(5:SEQNUM,2,FI,RESTART=(1,4)))
OUTFIL BUILD=(1,80)
/*
|
What i mean is, i submitted this job, and it gave "Syntax error". Going through the manual, it said FI is not supported for SEQNUM. Only BI, ZD and PD values are supported.
The problem with BI is that when i reload back to the table, it has to be converted back to FI, if i understand correctly. Is there an easy way to handle this, other than doing through 2 steps. |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
If you needed to "convert" it could be done with another simple WHEN=INIT in the OUTREC.
You've avoided the other questions. If you "smallint" can only be positive, there is no difference between BI and FI. If your sequence can... "go negative", what would that mean? |
|
| Back to top |
|
|
nartcr
New User
Joined: 06 Jun 2007
Posts: 83
Location: anada
|
|
|
|
| Bill Woodger wrote: |
If you needed to "convert" it could be done with another simple WHEN=INIT in the OUTREC.
You've avoided the other questions. If you "smallint" can only be positive, there is no difference between BI and FI. If your sequence can... "go negative", what would that mean? |
The value in small int is from 1 to 32767.. no negative values in db. Let me try to run the job next Monday and post the results here. |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| FI is signed. -32,768 to +32,767. BI is unsigned. 0-65,535. If you don't have negative sequence numbers, and if they can't exceed 32,767, for your specific purpose FI and BI are the same, and you will have no problems. |
|
| Back to top |
|
|
nartcr
New User
Joined: 06 Jun 2007
Posts: 83
Location: anada
|
|
|
|
| Thanks Bill. I changed the query from FI to BI and it worked seamlessly. I will take some time to refresh my memory on FI and BI data formats. |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2286
Location: USA
|
|
|
|
| nartcr wrote: |
| I will take some time to refresh my memory on FI and BI data formats. |
The idea of "unsigned binary" is known mainly among SYNCSORT/Assembler. AFAIK the most of other tools do only consider "signed binary".
The "FI,2" in SYNCSORT is equivalent to "DS H" in Assembler, and SMALLINT in DB2.
The "BI,2" in SYNCSORT is usually equivalent to "DS AL2", or "DS XL2" in Assembler, and nothing in DB2.
Any value between +32,768 to +65,535 appeared as "unsigned binary" is valid as "signed binary", too, but considered as values between -32,768 to -1 (in descending order).
The values between 0 to +32767 are absolutely equivalent in both cases. It's very unlikely that the noted TEST_SID would exceed +32767. Even if it does, I bet it is used for the purposes of uniqueness within the same TEST_ID value, so it should not harm anything. |
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
