IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Breakup data in single row to multiple rows in diffent table


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Wed Jul 06, 2011 1:49 pm
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
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
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: 1020
Location: India

PostPosted: Wed Jul 06, 2011 9:52 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts INCLUDE OMIT COND for Multiple values... DFSORT/ICETOOL 5
No new posts Load new table with Old unload - DB2 DB2 6
No new posts To get the count of rows for every 1 ... DB2 3
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Data set Rec-Cnt and Byte-Cnt Testing & Performance 2
Search our Forums:

Back to Top