Page 1 of 2

Getting extra bit after the first field while unloading data

PostPosted: Tue Mar 11, 2014 3:45 pm
by shankar_dh
Hi ,
I am unloading the data from DB2 table using DB2BATCH program and capturing the unloaded data into SYSREC00 dataset by passing SQLs through SYSIN.

Please see the attached word document where I have explained my problem clearly.

Re: Getting extra bit after the first field while unloading

PostPosted: Tue Mar 11, 2014 4:15 pm
by enrico-sorichetti
most probably You have NULLABLE columns

Re: Getting extra bit after the first field while unloading

PostPosted: Tue Mar 11, 2014 5:05 pm
by shankar_dh
Thanks Enrico for your kind reply. I checked for NULLABLE columns. But I don't have any, Below is the output for your reference..
SELECT SUBSTR(ACCOUNT_UNIQUE_ID,1,3)
,SUM(ACCOUNT_BALANCE)
,COUNT(ACCOUNT_UNIQUE_ID)
FROM TAB600
WHERE SUBSTR(ACCOUNT_UNIQUE_ID,1,3) IS NULL
GROUP BY SUBSTR(ACCOUNT_UNIQUE_ID,1,3)
;
DSNT404I SQLCODE = 100, NOT FOUND: ROW NOT FOUND FOR FETCH, UPDATE, OR

Re: Getting extra bit after the first field while unloading

PostPosted: Tue Mar 11, 2014 5:21 pm
by NicC
You checked for nullable columns and say you do not have any, so why select where account_unique_id is NULL? And if it is null how do you expect to take the first 3 bytes of null (which is nothing)?

Also, please do not post attachments - put everything in-line. Some people will not look at attachments, either because they cannot because of company policy or because of the fear of malicious software included in them or simply because 'why should they? Posting attachments makes it more difficult to process the post'.

Re: Getting extra bit after the first field while unloading

PostPosted: Tue Mar 11, 2014 5:59 pm
by shankar_dh
Hi Nic,

I am very sorry for posting my problem in the attachment. I am not able to put the images inline so I went for the attachment. I have just made sure I don't have any nullable columns, As expected I did not get any row.
But My problem is I am getting extra bit after the first field after UNLOADING data from DB2 table.
Below is my QUERY:-
SELECT SUBSTR(ACCOUNT_UNIQUE_ID,1,3)
,SUM(ACCOUNT_BALANCE) <-- ACCOUNT_BALANCE is DECIMAL(25,2)
,COUNT(ACCOUNT_UNIQUE_ID)
FROM TAB600

Output of the QUERY:-
----+----1----+----2----+--
***************************
CDA & â
CCC000000000000530400000000
3410000000000000172C0001800
----------------------------
Same Query on other table
SELECT SUBSTR(ACCOUNT_UNIQUE_ID,1,3)
,SUM(ACCOUNT_BALANCE) <-- ACCOUNT_BALANCE is DECIMAL(25,2)
,COUNT(ACCOUNT_UNIQUE_ID)
FROM TAB130

Output of the QUERY:-
----+----1----+----2----+
*************************
ADA
CCC0000000000000103000000
141000000000000003C000030

Here I am not getting that extra bit but why am I getting in the first QUERY??

Re: Getting extra bit after the first field while unloading

PostPosted: Tue Mar 11, 2014 6:35 pm
by shankar_dh
And also can you clarify from which byte the data for SUM(ACCOUNT_BALANCE), COUNT(ACCOUNT_UNIQUE_ID) starts in the unloaded data.

Output of the QUERY - Unloaded data:-
----+----1----+----2----+--
***************************
CDA & â
CCC000000000000530400000000
3410000000000000172C0001800

Re: Getting extra bit after the first field while unloading

PostPosted: Tue Mar 11, 2014 7:07 pm
by enrico-sorichetti
when posting data good manners suggest to use the CODE TAGS

Output of the QUERY - Unloaded data:-
----+----1----+----2----+--
***************************
CDA & â
CCC000000000000530400000000
3410000000000000172C0001800


the CODE TAGS force the use of a fixed font , which makes easier to look at data/sources

by the way did You notice that in one case You run the query against TAB600 and in the other against TAB130 ???

Re: Getting extra bit after the first field while unloading

PostPosted: Tue Mar 11, 2014 8:27 pm
by shankar_dh
Thanks Enrico for reposting with CODE TAGS.
Yes, I am querying 2 different tables but QUERY is same and the length of the fields in both tables are also same.
Data might be different but length wise I have the same fields in both tables.

Re: Getting extra bit after the first field while unloading

PostPosted: Tue Mar 11, 2014 8:28 pm
by enrico-sorichetti
why not post the definition of the tables ???

Re: Getting extra bit after the first field while unloading

PostPosted: Wed Mar 12, 2014 11:31 am
by shankar_dh
Table TAB600 Columns       Col Type   Length
LEDGER_ACCOUNT             VARCHAR   40
LEDGER_DESCRIPTION         VARCHAR   100
LEDGER_FLAG                CHAR      2
GL_ACCOUNT                 VARCHAR   40
DEBIT                      DECIMAL   25
CREDIT                     DECIMAL   25
ACCOUNT_UNIQUE_ID          VARCHAR   110
ACCOUNT_NUMBER             VARCHAR   110
ACCOUNT_BALANCE            DECIMAL   25

In TAB600, I am interested only in the fields ACCOUNT_UNIQUE_ID & ACCOUNT_BALANCE; Here don't have any KEY.
Table TAB130 Columns      Col Type Length
ACCOUNT_UNIQUE_ID          VARCHAR   110
ACCOUNT_NUMBER             VARCHAR   110
ACCOUNT_BRANCH             VARCHAR   40
PRODUCT_CODE               INTEGER   4
REGISTERED_PLAN_TYPE_CODE  INTEGER   4
REGISTERED_PLAN_NUMBER     VARCHAR   40
CURRENCY_CODE              INTEGER   4
INS_DET_CAT_TYPE_CODE      INTEGER   4
ACCOUNT_BALANCE            DECIMAL   25
ACCESSIBLE_BALANCE         DECIMAL   25
MATURITY_DATE              CHAR      8

In TAB130, I am interested only in the fields ACCOUNT_UNIQUE_ID & ACCOUNT_BALANCE; Here ACCOUNT_UNIQUE_ID is the KEY.