Getting extra bit after the first field while unloading data



IBM's flagship relational database management system

Getting extra bit after the first field while unloading data

Postby shankar_dh » Tue Mar 11, 2014 3:45 pm

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.
You do not have the required permissions to view the files attached to this post.
shankar_dh
 
Posts: 41
Joined: Fri Mar 22, 2013 1:00 pm
Has thanked: 1 time
Been thanked: 0 time

Re: Getting extra bit after the first field while unloading data

 

Re: Getting extra bit after the first field while unloading

Postby enrico-sorichetti » Tue Mar 11, 2014 4:15 pm

most probably You have NULLABLE columns
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2644
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 130 times

Re: Getting extra bit after the first field while unloading

Postby shankar_dh » Tue Mar 11, 2014 5:05 pm

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
shankar_dh
 
Posts: 41
Joined: Fri Mar 22, 2013 1:00 pm
Has thanked: 1 time
Been thanked: 0 time

Re: Getting extra bit after the first field while unloading

Postby NicC » Tue Mar 11, 2014 5:21 pm

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'.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 2690
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisys (almost)
Has thanked: 4 times
Been thanked: 105 times

Re: Getting extra bit after the first field while unloading

Postby shankar_dh » Tue Mar 11, 2014 5:59 pm

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??
shankar_dh
 
Posts: 41
Joined: Fri Mar 22, 2013 1:00 pm
Has thanked: 1 time
Been thanked: 0 time

Re: Getting extra bit after the first field while unloading

Postby shankar_dh » Tue Mar 11, 2014 6:35 pm

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
shankar_dh
 
Posts: 41
Joined: Fri Mar 22, 2013 1:00 pm
Has thanked: 1 time
Been thanked: 0 time

Re: Getting extra bit after the first field while unloading

Postby enrico-sorichetti » Tue Mar 11, 2014 7:07 pm

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 ???
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2644
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 130 times

Re: Getting extra bit after the first field while unloading

Postby shankar_dh » Tue Mar 11, 2014 8:27 pm

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.
shankar_dh
 
Posts: 41
Joined: Fri Mar 22, 2013 1:00 pm
Has thanked: 1 time
Been thanked: 0 time

Re: Getting extra bit after the first field while unloading

Postby enrico-sorichetti » Tue Mar 11, 2014 8:28 pm

why not post the definition of the tables ???
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2644
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 130 times

Re: Getting extra bit after the first field while unloading

Postby shankar_dh » Wed Mar 12, 2014 11:31 am

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.
shankar_dh
 
Posts: 41
Joined: Fri Mar 22, 2013 1:00 pm
Has thanked: 1 time
Been thanked: 0 time

Next

Return to DB2

 


  • Related topics
    Replies
    Views
    Last post