very large fields in As400 ISeries database

问题:

I would like to save a large XML string (possibly longer than 32K or 64K) into an AS400 file field. Either DDS or SQL files would be OK. Example of SQL file below.

CREATE TABLE MYLIB/PRODUCT
(PRODCODE DEC (5 ) NOT NULL WITH DEFAULT,
PRODDESC CHAR (30 ) NOT NULL WITH DEFAULT,
LONGDESC CLOB (70K ) ALLOCATE(1000) NOT NULL WITH DEFAULT)

We would use RPGLE to read and write to fields.

The goal is to then pull out data via ODBC connection on a client side.

AS400 character fields seem to have 32K limit, so this is not great option.

What options do I have? I have been reading up on CLOBs but there appear to be restrictions writing large strings to CLOBS and reading CLOB field remotely. Note that client is (still) on v5R4 of AS400 OS.

thanks!

---

Charles' answer below shows how to extract data. I would like to insert data. This code runs, but throws a '22501' SQL error.

D wLongDesc       s          65531a   varying                      
D longdesc        s                   sqltype(CLOB:65531)          

 /free                                                             
    //eval longdesc = *ALL'123';                                   
    eval Wlongdesc = '123';                                        

    exec SQL                                                       
    INSERT INTO PRODUCT (PRODCODE, PRODDESC, LONGDESC)             
    VALUES (123, 'Product Description', :LongDesc );               

    if %subst(sqlstt:1:2) <> '00';                                 
       // an error occurred.                                       
    endif;                                                         

    // get length explicitly, variables are setup by pre-processor 
    longdesc_len = %len(%trim(longdesc_data));                     

    wLongDesc = %subst(longdesc_data:1:longdesc_len);              

 /end-free                                                         
C                   Eval      *INLR = *on                          
C                   Return                                         

Additional question: Is this technique suitable for storing data which I want to extract via ODBC connection later? Does ODBC read CLOB as pointer or can it pull out text?

回答1:

At v5r4, RPGLE actually supports 64K character variables.

However, the DB is limited to 32K for regular char/varchar fields.

You'd need to use a CLOB for anything bigger than 32K.

If you can live with 64K (or so )

CREATE TABLE MYLIB/PRODUCT
(PRODCODE DEC (5 ) NOT NULL WITH DEFAULT,
PRODDESC CHAR (30 ) NOT NULL WITH DEFAULT,
LONGDESC CLOB (65531) ALLOCATE(1000) NOT NULL WITH DEFAULT)

You can use RPGLE SQLTYPE support

 D code            S              5s 0
 d wLongDesc       s          65531a   varying
 D longdesc        s                   sqltype(CLOB:65531)

  /free
   exec SQL
     select  prodcode, longdesc
      into :code, :longdesc
      from mylib/product
     where prodcode = :mykey;

   wLongDesc = %substr(longdesc_data:1:longdesc_len);
   DoSomthing(wLongDesc);

The pre-compiler will replace longdesc with a DS defined like so:

 D longdesc        ds
 D  longdesc_len                 10u 0
 D  longdesc_data             65531a

You could simply use it directly, making sure to only use up to longdesc_len or covert it to a VARYING as I've done above.

If absolutely must handle larger than 64K...

1. Upgrade to a supported version of the OS (16MB variables supported)
2. Access the CLOB contents via an IFS file using a file reference

Option 2 is one I've never seen used....and I can't find any examples. Just saw it mentioned in this old article.. http://www.ibmsystemsmag.com/ibmi/developer/general/BLOBs,-CLOBs-and-RPG/?page=2

回答2:

This example shows how to write to a CLOB field in Db2 database... with help from Charles and Mr Murphy's feedback.

 * ----------------------------------------------------------------------         
 * Create table with CLOB:
 *   CREATE TABLE MYLIB/PRODUCT 
 *   (MYDEC  DEC (5 ) NOT NULL WITH DEFAULT,                                        
 *   MYCHAR CHAR (30 ) NOT NULL WITH DEFAULT,                                       
 *   MYCLOB CLOB (65531) ALLOCATE(1000) NOT NULL WITH DEFAULT)                      
 * ----------------------------------------------------------------------         

D PRODCODE        S              5i 0                                             
D PRODDESC        S             30a                                               

D i               S             10i 0                                             
D wLongDesc       s          65531a   varying                                     
D longdesc        s                   sqltype(CLOB:65531)                         

D* Note that variables longdesc_data and longdesc_len                             
D* get create automatocally by SQL pre-processor.                                 

 /free                                                                   
   eval wLongdesc = '123';                                              

   longdesc_data = wLongDesc;                                           
   longdesc_len = %len(%trim(wLongDesc));                               

   exec SQL set option commit = *none;                                  

   exec SQL                                                             
    INSERT INTO PRODUCT (MYDEC, MYCHAR, MYCLOB)                         
   VALUES (123, 'Product Description',:longDesc);                       

   if %subst(sqlstt:1:2)<>'00' ;          
      // an error occurred.               
   endif;                                 

   Eval *INLR = *on;                      
   Return;                                
 /end-free