With nearly all folks AS400 programmers dealing together with huge quantities of XML data inside our ports between software within our AS400 systems and between different platforms, or using data originating out of ports to web services running on different systems or platforms, we struck the situation of discovering a means to save this data from our db2/400 database. By this moment, DB2/400 does not let us save or manipulate XML documents natively from the database (even though that really is allowed for i5/os V7R1) 192.168.0.1.1.
This really is a easy issue to solve if we have been getting the database from coffee using jdbc and may control CLOBS, however if we’re utilizing RPG to gain access to the database, then we’re restricted within our own access into the AS400 database along with CLOBS. The following matter may be that the album length on the db2 document is confined to just under 32K.
We can save considerable quantities of data from the
database
, even in the sort of LOBs(Large Objects). The sort we may utilize to the XML storage purposes could be your CLOB, that may take personality data for example XML.
We may utilize embedded SQL from our RPG apps to place our XML data in to a database clob areas, however we’ve any difficulties. We can save upto 2Gb of data at a CLOB. RPG has an area size maximum of 64K around V5R4 (16M on V6R1), however there’s not a straightforward to accessing our 64K of data within a RPG field to an database CLOB.
As an example below, centered on you from Scott Klement, shows how you are able to access data from the AS400 RPG field in to the CLOB.
Even the X/Open SQL Call Level Interface is a standard for direct accessibility to using a systems SQL Engine with no precompile, we’ll use it by calling the SQL CLI apis out of RPG instead of using embedded SQL. It permits usage of SQL works directly during procedure calls for an agency application given from DB2 over the AS400. Utilizing the SQL Call Level Interface procedure calls gives one to organize SQL statements, and execute SQL statements, and draw rows of information, and also do complex functions like obtaining the catalogs, and also binding application factors to output column.
Therefore with the CLI, we can skip the restriction of AS400 embedded SQL, and then now pass our 64K of data just as a server factor by means of a pointer. A good illustration that demonstrates the way to upgrade a CLOB with this method is shown below:
/comprise Decisionlibl/qtxtsrc,MGSQLCLI_H
************************************************************************
* updateClob – Update Clob
************************************************************************
P updateClob B Export
D updateClob pi 10i 0
d Id 10i 0 const
d e msg 65535A const
D identification s 10s 0
D ptr s *
D rc s 10i 0
D msgLen s 10i 0
D msgSize s 10i 0
D env S-like(SQLHENV) inz(SQL_NULL_HENV)
d e xmsg s 65535A
/Free
// Create a SQL surroundings & link manage
SQLAllocEnv(env);
SQLSetEnvAttrI(env:SQL_ATTR_OUTPUT_NTS:
SQL_FALSE: percentdimensions(SQLINTEGER));
SQLSetEnvAttrI(env: SQL_ATTR_ENVHNDL_COUNTER
:SQL_TRUE: percentdimensions(SQLINTEGER));
SQLAllocConnect(env: conn);
SQLSetConnectAttrI(conn: SQL_ATTR_DBC_SYS_NAMING
:SQL_TRUE: percentdimensions(SQLINTEGER));
// Set Commitment Level to *CHG
SQLSetConnectAttrI(conn: SQL_ATTR_COMMIT
:SQL_COMMIT_CHG: percentdimensions(SQLINTEGER));
SQLConnect(conn: ”*LOCAL’:SQL_NTS:*NULL
:SQL_NTS:*NULL:SQL_NTS);
// Create a SQL statement
// Assign announcement parameters
Ptr = percentaddr(xmsg);
msgLen = percentlen(xmsg);
msgSize = percentsize(xmsg);
rc = SQLBindParam(stmt:1:SQL_CLOB:SQL_CLOB:msgSize
:0:ptr:msgLen);
: percentlen(identification): percentdecpos(identification): percentaddr(identification):0);
// Free up SQL Resources
SQLFreeStmt( stmt: SQL_DROP );
Cleanup();
return rc;
/End-Free
De E
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
* Cleanup(): Deallocate/Disconnect SQL CLI manages
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
P cleansing B
D clean up pi
/complimentary
if (stmt SQL_NULL_HSTMT);
SQLFreeStmt(stmt: SQL_DROP);
stmt = SQL_NULL_HSTMT;
endif;
if (conn SQL_NULL_HDBC);
SQLDisconnect(conn);
SQLFreeConnect(conn);
conn = SQL_NULL_HDBC;
endif;
/ /End-Free
de E
Thus with the example above it ought to be fairly straightforward for a programmer to upgrading an AS400 db2 CLOB out of the 64K RPG Variable with the SQL Call Level Interface.
