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.
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?
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.
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.
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?
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.
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.
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.