Page 1 of 2
Getting extra bit after the first field while unloading data
Posted:
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
Posted:
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
Posted:
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
Posted:
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
Posted:
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
Posted:
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
Posted:
Tue Mar 11, 2014 7:07 pm
by enrico-sorichetti
when posting data good manners suggest to use the
CODE TAGSOutput 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
Posted:
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
Posted:
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
Posted:
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.