Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Breakup data in single row to multiple rows in diffent table

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Jul 06, 2011 1:49 pm    Post subject: Breakup data in single row to multiple rows in diffent table
Reply with quote

Hi,

I have data like this, ColA(int) and ColB(varchar(1024))
Code:
ColA   ColB
0    05UVWXY06ZABCD07EFGHI08JKLMN09OPQRS10ZZZZZ99NOPEE99zzzzz
1    01ABCDE02FGHIJ03KLMNO04PQRST99ZZZZZ99NOPEE99zzzzz99zzzzz
2    11TUVWX12YZABC13DEFGH14IJKLM99ZZZZZ99NOPEE99zzzzz99zzzzz       

In ColB, data are in multiple sets like, first 2 characters are numeric and later 5 chars are alphabetic, now this multiple sets of data in a row needs to be inserted into a separate table in different rows and any data after 99(as 2 first chars in a set)can be ignore/discared in a row.

New table should have have data like,
Code:
ColA   ColB   ColC
0   05   UVWXY
0   06   ZABCD
0   07   EFGHI
0   08   JKLMN
0   09   OPQRS
1   01   ABCDE
1   02   FGHIJ
.
.
.

Currently i have following 2 Options,
I can prepare query like below using unions, since the row has around 88 sets of data, 88 unions might be required(not the best method, 88 tablespace scans, noway)
Code:
   SELECT ID,SUBSTR(DUMMY_TEXT,1,2), SUBSTR(DUMMY_TEXT,3,5) 
   FROM BOBT1.TRY1                                           
   UNION ALL                                                 
   SELECT ID,SUBSTR(DUMMY_TEXT,8,2), SUBSTR(DUMMY_TEXT,10,5)
   FROM BOBT1.TRY1                                           

One-time conversion cobol program(only the best method, i can think of right now, because will involve only one tablespace scan).

I just want to know, if there is anyother way of doing it.

Thanks,
Sushanth
Back to top
View user's profile Send private message

Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Wed Jul 06, 2011 2:32 pm    Post subject:
Reply with quote

I would do like this..

1: unload the first table
2: apply dfsort to input file get output which can be loaded to new table
3: load new table with output of second step...
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Jul 06, 2011 6:25 pm    Post subject:
Reply with quote

I'm not sure if the following solution won't do 88 tablescans, it depends on the number of rows:

* create a table tpos with 3 integer columns (nr, strt1,strt2)
you can create this table in qmf with the following query :
Code:
with cte(cnt,strt1,strt2) as (
select 1,1,3 from sysibm.sysdummy1
union all
select cnt + 1, strt1 + 7 ,strt2 + 7 from cte
where cnt < 88)
select * from cte


* then execute this query :
Code:
insert into result1 (
select colA, integer(substr(colb,strt1,2)) as colBB, substr(colb,strt2,5) as colCC
from
   Tpos
   ,t1
where   substr(colb,strt1,2) <> '99'
  and   strt1 < length(colb))
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Jul 06, 2011 9:52 pm    Post subject:
Reply with quote

Wow that's really superb, GuyC
The Real table has 20 million rows, Size of this Varchar column is 21000 and it contains mixture of DATE, CHAR and cobol COMP-3 values stored directly into this column. So, currently the query involves joins, conversions from COMP-3 to numeric and other things, there will be huge number of functions involved.

I will check up with the test data with this approach.

Thank You Very Much GuyC,

Sushanth
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Replace values in the input data Vikas Maharnawar DFSORT/ICETOOL 10 Thu May 11, 2017 2:18 pm
No new posts Unable to create multiple files using... mbattu COBOL Programming 3 Fri May 05, 2017 5:35 pm
No new posts Group Data based on a key Arun Raj DFSORT/ICETOOL 7 Thu Apr 27, 2017 11:29 pm
No new posts unload data from table with lob columns farhad_evan DB2 0 Sat Apr 22, 2017 1:32 pm
This topic is locked: you cannot edit posts or make replies. SDSF multiple spool datasets extracte... PJAlarcon CLIST & REXX 1 Fri Apr 21, 2017 10:50 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us