CODEWITHSUNDEEP
db2ibm-midrangeclobdb2-400rpgle
Mustapha George
Mustapha George

Reputation: 2527

How to build a large string in RPG and write it out to DDL CLOB field

follow up to very large fields in As400 ISeries database ...

I want to build a large string in RPG and write it out to DDL CLOB field.

However even if I use a CLOB field in RPG code and a DDL CLOB field, I seem to be limited to 65531 characters, because data passes through an interim string value call 'wkClob_data'. Can I get over this limitation? maybe using a User Space?? [Still on V5R4]

DDL: 

CREATE TABLE MYLIB/MYFILE                                      
(MYDEC  DEC  (5)  NOT NULL WITH DEFAULT,                       
 MYCHAR CHAR (30) NOT NULL WITH DEFAULT,                        
 MYCLOB CLOB (100000) ALLOCATE(1000) NOT NULL WITH DEFAULT)

RPG:

D wkValue         S          65530a   varying                     
D wkClob          S                   sqltype(CLOB:65530)         


//  file up wkClob_data with a big string..
Eval wkClob_data = someData + %trim(moreData);                 
Eval wkClob_len = %len(%trim(someData + %trim(moreData)));     

exec SQL                                      
INSERT INTO MYFILE (MYDEC, MYCHAR, MYCLOB)    
VALUES (123, 'Some Description',:wkClob);

Upvotes: 0

Views: 2961

Answers (3)

jmarkmurphy
jmarkmurphy

Reputation: 11473

According to the v5.4 Knowledge Center you have two options for fields bigger than a valid host variable.

  1. Use a Locator field
  2. Use a File field

With the Locator field, DB2 returns a handle to the Large Object (LOB), and the data stays on the server. SQL statements can be used to substring or search the LOB into other host variables. Examples exist in the Knowledge center for C and COBOL, but the embedded SQL should remain the same for RPG.

Here is the Locator example from the Knowledge center translated to RPG IV

dcl-s empnum        Char(6);
dcl-s resume        SqlType(clob_locator);
dcl-s lobind        Int(5);
dcl-s di_buffer     SqlType(clob_locator);
dcl-s buffer        SqlType(clob: 1024);
dcl-s di_begin_loc  Int(20);
dcl-s di_end_loc    Int(20);

dcl-proc lobloc;

exec sql
  declare C1 cursor for
    select empno, resume
      from emp_resume
      where resume_format = 'ascii'
      and empno <> 'A00130';

exec sql open C1;      

buffer_len = 0;

dow 1=1;
  exec sql fetch C1 into :empnum, :resume :lobind;
  if sqlcode <> 0;
    // error, warning, or no data
    leave;
  endif;

  if lobind < 0;
    // LOB value is null
    iter;
  endif;

  // Find the "Department Information" section
  exec sql 
    values (posstr(:resume, 'Department Information'))
      into :di_begin_loc;

  // Find the "Education" section
  exec sql 
    values (posstr(:resume, 'Education'))
      into :di_end_loc;

  // Get the Department information only into a new LOB    
  exec sql    
    values (substr(:resume, :di_begin_loc, :di_end_loc - :di_begin_loc))
      into :di_buffer;

  // Append the department info into a new CLOB field    
  exec sql    
    values (:buffer || :di_buffer) into :buffer;
enddo;  

exec sql free locator :resume, :di_buffer;

return;
end-proc;

Note you can insert or update LOB fields using a locator as well as the select shown.

With the File field, DB2 transfers the contents of the LOB to or from a file in the IFS. Stream file API's can be used to read or write the file. There is an example similar to the locator example for LOB Files in the Knowledge Center.

Upvotes: 0

Charles
Charles

Reputation: 23793

Where's the data coming from?

Does it really have to go through an RPG program? C/C++ are also available on the IBM i. A hammer is always the right tool, not everything is a nail.

If you're stuck with RPG, the following might work...

D wkValue         S          65530a   varying                     
D wkClob1         S                   sqltype(CLOB:65530)         
D wkClob2         S                   sqltype(CLOB:65530)         

//<snip>

exec SQL                                      
INSERT INTO MYFILE (MYDEC, MYCHAR, MYCLOB)    
VALUES (123, 'Some Description',:wkClob1 CONCAT :wkClob2);

But honestly, the right way would probably be to take advantage of the SQL call level interface (SQL CLI) directly.

The Who Knew You Could Do That with RPG IV? Redbook has some code information about using CLI from RPG.

Upvotes: 2

danny117
danny117

Reputation: 5651

I'm guessing what I might try in SQLRPGLE before I gave up and made a java class that I could call from RPG.

exec SQL                                      
INSERT INTO MYFILE (MYDEC, MYCHAR, MYCLOB)    
VALUES (123, 'Some Description',:wkClob || :wkclob1 || :wkclob2);     

exec SQL
    update myfile set substring(myclob:32323412:11) = 'Hello World';

Upvotes: 1

Related Questions