View previous topic :: View next topic
|
Author |
Message |
sonia wen
New User
Joined: 15 May 2010 Posts: 3 Location: Toronto
|
|
|
|
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 |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
sonia wen
New User
Joined: 15 May 2010 Posts: 3 Location: Toronto
|
|
|
|
Thanks!!! |
|
Back to top |
|
|
don.leahy
Active Member
Joined: 06 Jul 2010 Posts: 765 Location: Whitby, ON, Canada
|
|
|
|
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 |
|
|
cyrus.e.cabrera
New User
Joined: 25 Jul 2011 Posts: 14 Location: Philippines
|
|
|
|
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 |
|
|
sonia wen
New User
Joined: 15 May 2010 Posts: 3 Location: Toronto
|
|
|
|
Thanks for all the posting! The first suggestion worked (after first line 72 bytes full, just continue with next line). |
|
Back to top |
|
|
|