Re: RUNSQLSTM Source File Record Length

A recursive query could break the lines. The following breaks lines on each semicolon for example [with assumed/implicit vs explicit varchar casting]:

with
manyline ( nextsplit ,thisline ,nextline ) as
( select cast( locate(';' ,srcdta ) + 1 as int ) as nextsplit
,substr( srcdta , 1 ,locate(';', srcdta ) ) as thisline
,substr( srcdta ,locate(';', srcdta ) + 1 ) as nextline
from oneline
union all
select cast( locate(';', nextline) + 1
+ nextsplit as int ) as nextsplit
,substr( nextline, 1 ,locate(';', nextline) ) as thisline
,substr( nextline,locate(';', nextline) + 1 ) as nextline
from manyline
where nextline <> ''
)
select thisline from manyline
order by nextsplit

Regards, Chuck
-- All comments provided "as is" with no warranties of any kind whatsoever and may not represent positions, strategies, nor views of my employer

Mike Krebs wrote:

Interesting question overall...

There is support writing multiple records at one time in db2 SQL. If you
just had a string, you could do something like this:

INSERT INTO QTEMP/MAK (TEST1) VALUES (substr('1234567890abcdefghij',1,10)), (substr('1234567890abcdefghij',11,10))
But, I can't figure out how to use a select statement to drive multiple
values.

Googling finds several references to folks that have tried creating multiple
records from a single record (for example, separating a comma delimited list
into separate records). They all look quite "messy". Creating a function or
some kind of temporary table might be an answer. There seem to a couple of
"split" functions that folks have created that would have potential.

If you don't get an answer here, try Howard Arner or Ted Holt at
itjungle.com. They both seem to relish a SQL challenge.

Mike