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

DB2 SPUFI Insert: a long text column cannot fit into 1 line


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

New User


Joined: 15 May 2010
Posts: 3
Location: Toronto

PostPosted: Wed Feb 16, 2011 7:47 pm
Reply with quote

Hi,
I need to insert a DB2 record using SPUFI. A colume is defined as char(254) and I cannot fit the 254 bytes text within one line. What I can do to split one column content into more than one line?

The field is defined as char(254):
XSL_URL CHAR(254) NOT NULL WITH DEFAULT,

Thanks!
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Wed Feb 16, 2011 8:16 pm
Reply with quote

Just code the data out to col 72 and then continue at col 1 of the next line. There is no continuation indicator SPFUI just concatenates the lines together until it encounters a semi-colon.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Feb 16, 2011 8:19 pm
Reply with quote

after the intial insert,
(which will contain as much of the literal as you can input,
use multiple UPDATE statements.

Code:

INSERT into your.table
 values('first 32 bytes of text')
;
commit
;
UPDATE your.table
  SET your.column = SUBSTR(your.column,1,32) !! '2nd 32 bytes of text'
;
commit
;
UPDATE your.table
  SET your.column = SUBSTR(your.column,1,64) !! '3rd 32 bytes of text'
;
commit
;


until you have the complete 254 bytes. you can use larger than 32,
but 32 was easy to remember as i increased the SUBSTR length parm.
Back to top
View user's profile Send private message
sonia wen

New User


Joined: 15 May 2010
Posts: 3
Location: Toronto

PostPosted: Wed Feb 16, 2011 9:21 pm
Reply with quote

Thanks!!!
Back to top
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada

PostPosted: Thu Feb 17, 2011 2:20 am
Reply with quote

I don't want to drift too far off topic, but the input data set LRECL restrictions imposed by SPUFI have bugged me for years.

It is important to note that this is not a DB2 restriction, it is an artificial restriction imposed by the SPUFI utililty.

Once you realize this the workaround becomes quite straightforward if you have some DSNREXX knowledge. Years ago I wrote a simple Rexx program that reads an SQL statement from an input file (of whatever LRECL) and passes it to DB2, unaltered, for processing via EXECUTE IMMEDIATE.

You can't use EXECUTE IMMEDIATE for SELECT statements, but for INSERT, UPDATE and DELETE it works great.
Back to top
View user's profile Send private message
cyrus.e.cabrera

New User


Joined: 25 Jul 2011
Posts: 14
Location: Philippines

PostPosted: Mon Jun 02, 2014 12:35 pm
Reply with quote

This might be a little late, but you'll never know; some people might still be looking up on HOW exactly can you fit a long INSERT value inside SPUFI's limitation of 1-72 lines.


What I did is by using the concat symbol "||".


example:

Code:
INSERT INTO <YOUR TABLE NAME>
       (A_VERY_LONG_256_BYTE_FIELD)
        VALUES
       ('THIS LONG VALUE THAT WILL ALMOST REACH 72 bytes'||  <-- look at that handy double pipe symbol :D
        'CONTINUATION OF THE LONG LOST FIELD');
     
COMMIT; <- just for obssessive purposes.


Long story short, it's exactly up to you how would you fit "n" number of bytes in one field; but you get the essence, whenever you want to indicate that "next" line is still a part of the previous line, you mimic the example above.
Back to top
View user's profile Send private message
sonia wen

New User


Joined: 15 May 2010
Posts: 3
Location: Toronto

PostPosted: Mon Jun 02, 2014 7:22 pm
Reply with quote

Thanks for all the posting! The first suggestion worked (after first line 72 bytes full, just continue with next line).
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 DELETE SPUFI DB2 1
No new posts Write line by line from two files DFSORT/ICETOOL 7
No new posts How to load to DB2 with column level ... DB2 6
No new posts Adding QMF and SPUFI to the ISPF menu DB2 20
No new posts RC query -Time column CA Products 3
Search our Forums:

Back to Top