Updating an as400 db2 CLOB with a 64K RPG Variable

With most of us working with large amounts of XML data in our interfaces between applications on our as400 systems or with XML data coming from web services running on other systems, we run up against the issue of how to get this data into the database.

This is a fairly straightforward issue to deal with if we are working with java, but if we are working with good old RPG, how do we our XML data into the as400 database. Especially as the record length on a db2 file is restricted to 32K.

We do have a facility for storing large amounts of data in the database, in the form of LOBs(Large Objects). The type we are specifically interested in is the CLOB, as we can store character data such as XML in there.

In theory we should be able to use embedded SQL in our RPG programs to put our XML data into a database clob fields. We can store up to 2Gb of data in our CLOB. Unfortunately we have another issue. Although RPG has a field size maximum of 64K on V5R4 (16M on V6R1 I believe), we have no simple way of getting our 64K of data in out RPG field into our CLOB.

Looking around online there were plenty of examples of how to read data in from a CLOB, and how to save CLOB data to an IFS file. But there was only one example, from Scott Klement, that showed how you could get data from your as400 RPG field into the CLOB.

The way this is achieved is to bypass embedded SQL and use the SQL Call Level Interface directly. The X/Open SQL Call Level Interface is a standard for direct access the a systems SQL Engine without a precompile. It allows access to SQL functions directly through procedure calls to a service program provided by DB2 on the as400. Using the SQL Call Level Interface procedure calls allows you to prepare SQL statements, execute SQL statements, fetch rows of data, and even do advanced functions such as accessing the catalogs, and binding program variables to output columns.

So using the CLI, we can bypass the limitation of embedded SQL, and pass our 64K of data as a host variable using a pointer. An example that shows how to update a CLOB using this method is shown below

/include *libl/qtxtsrc,MGSQLCLI_H

************************************************************************
* updateClob - Update Clob
************************************************************************
P updateClob B Export
D updateClob pi 10i 0
d Id 10i 0 const
d msg 65535A const

D id 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 xmsg s 65535A
/Free

xmsg = msg;

// Create an SQL environment & connection handle
SQLAllocEnv(env);
SQLSetEnvAttrI(env:SQL_ATTR_OUTPUT_NTS:
SQL_FALSE:%size(SQLINTEGER) );
SQLSetEnvAttrI(env: SQL_ATTR_ENVHNDL_COUNTER
:SQL_TRUE: %size(SQLINTEGER) );
SQLAllocConnect(env: conn);

// Set Naming Format
SQLSetConnectAttrI(conn: SQL_ATTR_DBC_SYS_NAMING
:SQL_TRUE: %size(SQLINTEGER) );

// Set Commitment Level to *CHG
SQLSetConnectAttrI(conn: SQL_ATTR_COMMIT
:SQL_COMMIT_CHG: %size(SQLINTEGER) );

// Connect to Database
SQLConnect(conn:'*LOCAL':SQL_NTS:*NULL
:SQL_NTS:*NULL:SQL_NTS );

// Create an SQL statement
SQLAllocStmt(conn: stmt );
rc = SQLPrepare(stmt
: 'UPDATE FILE SET mymsg = ? WHERE myid = ?'
: SQL_NTS );

// Assign statement parameters
ptr = %addr(xmsg);
msgLen = %len(xmsg);
msgSize = %size(xmsg);
rc = SQLBindParam(stmt:1:SQL_CLOB:SQL_CLOB:msgSize
:0:ptr:msgLen);

rc = SQLBindParam(stmt:2:SQL_NUMERIC:SQL_NUMERIC
:%len(id):%decpos(id): %addr(id):0);

// Execute Statement
rc = SQLExecute(stmt);

// Free up SQL Resources
SQLFreeStmt( stmt : SQL_DROP );

cleanup();
return rc;

/End-Free
p E
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
* Cleanup(): Deallocate/Disconnect SQL CLI handles
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
P cleanup B
D cleanup pi
/free
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
p E

Updating an as400 db2 CLOB with a 64K RPG Variable

iSeries DB2 catalog tables and views

iSeries catalog tables and views

The iSeries™ catalog includes the following views and tables in the QSYS2 schema:

DB2 UDB for iSeries name Corresponding ANSI/ISO name Description
SYSCATALOGS CATALOGS Information about relational databases
SYSCHKCST CHECK_CONSTRAINTS Information about check constraints
SYSCOLUMNS COLUMNS Information about column attributes
SYSCST TABLE_CONSTRAINTS Information about all constraints
SYSCSTCOL CONSTRAINT_COLUMN_USAGE Information about the columns referenced in a constraint
SYSCSTDEP CONSTRAINT_TABLE_USAGE Information about constraint dependencies on tables
SYSFUNCS ROUTINES Information about user-defined functions
SYSINDEXES  Information about indexes
SYSJARCONTENTS  Information about jars for Java™ routines.
SYSJAROBJECTS  Information about jars for Java routines.
SYSKEYCST KEY_COLUMN_USAGE Information about unique, primary, and foreign keys
SYSKEYS  Information about index keys
SYSPACKAGE  Information about packages
SYSPARMS PARAMETERS Information about routine parameters
SYSPROCS ROUTINES Information about procedures
SYSREFCST REFERENTIAL_CONSTRAINTS Information about referential constraints
SYSROUTINES ROUTINES Information about functions and procedures
SYSROUTINEDEP ROUTINE_TABLE_USAGE Information about function and procedure dependencies
SYSSEQUENCES  Information about sequences
SYSTABLEDEP  Information about materialized query table dependencies
SYSTABLES TABLES Information about tables and views
SYSTRIGCOL TRIGGER_COLUMN_USAGE Information about columns used in a trigger
SYSTRIGDEP TRIGGER_TABLE_USAGE Information about objects used in a trigger
SYSTRIGGERS TRIGGERS Information about triggers
SYSTRIGUPD TRIGGERED_UPDATE_COLUMNS Information about columns in the WHEN clause of a trigger
SYSTYPES USER_DEFINED_TYPES Information about built-in data types and distinct types
SYSVIEWDEP VIEW_TABLE_USAGE Information about view dependencies on tables
SYSVIEWS VIEWS Information about definition of a view

Using Reserved Words in as400 sql queries

If your trying to run an SQL statement on the as400, but are getting errors because one of your column names is an DB2/400 SQL reserved word, try the following.

Wrap double quotes around the offending column name, and also make it uppercase. This should resolve the reserved words problem!

Striving for Optimal Journal Performance on DB2 Universal Database for iSeries

http://www.redbooks.ibm.com/abstracts/SG246286.html?Open

Journaling is an inherent feature of OS/400 on the iSeries that has been around since the early releases of the System/38. The system employs journals to ensure data integrity and recoverability and this support can further be extended to reduce the duration of an abnormal system end. Journaling also serves as the cornerstone upon which many data replication and data warehouse refresh schemes are built.
The aim of this IBM Redbook is to provide you with an understanding of the factors which influence journal performance on the iSeries and to help you identify which hardware options and software settings you can employ to minimize this impact. We will also share with you some ideas on how to minimize the impact of journaling on your iSeries from an application point of view.
We also explain the remote journal function on the OS/400, which offers a reliable and fast method to transfer journal entries to a remote iSeries server. We performed tests concerning remote journal performance and tested this function in different environments

Heterogeneous data access for i5/OS applications - IBM WebSphere Federation Server

Discusses the available methods for i5/OS programs to access data that is not stored in DB2 (such as Oracle and SQL Server)

https://www-304.ibm.com/jct09002c/partnerworld/wps/servlet/ContentHandler/whitepaper/i5os/db2/access

Debugging SQL procedures with the System i graphical debugger and DB2 developers workbench

IBM® DB2® for i5/OS® first simplified the debugging of SQL procedures, functions and triggers in the
IBM OS/400® Version 5 Release 2 operating system with the introduction of the SQL *SOURCE Debug
View. (See the article entitled “Graphical Debugger Makes Procedural SQL Debug Even Easier,”
published in the March 2003 issue of iSeries NEWS:
www.systeminetwork.com/artarchive/16027/Graphical_Debugger_Makes_Procedural_SQL_Debug_Even
_Easier.html.) In conjunction with the IBM Toolbox for Java™ System i System Debugger, this debug
view greatly simplified the debugging of SQL procedural objects.
With the DB2 for i5/OS V5R4 enhancements, developers now have another option when they need to
graphically debug SQL procedures. These new debug capabilities are provided by the IBM DB2
Developer’s Workbench (formerly known as IBM DB2 Development Center). The Developer Workbench
is a visual tool that supports the entire DB2 family across all IBM platforms for the rapid development of
DB2 business objects. The DB2 Developer Workbench only supports the debugging of SQL and Java
stored procedures.

https://www-304.ibm.com/jct09002c/partnerworld/wps/servlet/ContentHandler?contentId=cntkv8_XCRndVf4MDAD&localeName=en

DB2 for i5/OS and Web Services

This paper will show the reader how to implement a DB2 Web service

http://www-03.ibm.com/servers/enable/site/education/abstracts/db2web_abs.html

IBM DB2 Development Add-in for Microsoft Visual Studio .NET

Discusses using the DB2 Developer Add-in for Visual Studio.NET and the ADO.NET classes to access .NET applications on IBM i

http://www-03.ibm.com/servers/enable/site/education/abstracts/4606_abs.html

Creating and using materialized query tables (MQT) in IBM DB2 for i5/OS

MQT offers new methods for enjoying high performance query processing

http://www-03.ibm.com/servers/enable/site/education/abstracts/438a_abs.html

Building Web services with ibm i db2 and Microsoft Visual Studio .NET

Build and test Web services integrating existing i5/OS code and DB2 .Net in Microsoft Visual Studio integrated development environment

http://www-03.ibm.com/servers/enable/site/education/abstracts/e102_abs.html

Accessing DB2 for i5/OS data from a Linux Partition and Optimizing Linux access to your DB2 for i5/OS database

Discusses choosing the right JDBC driver to access DB2 for i5/OS from a Linux partition and also provides tips and examples on optimizing the JDBC access to your DB2 for i5/OS database

http://www-03.ibm.com/servers/enable/site/education/abstracts/1eee_abs.html

A new approach to enforcing business rules with DB2 for IBM i

By declaring your business rules in the database, you're assured that those requirements are enforced across all transactions and, more importantly, all interfaces

http://www-03.ibm.com/servers/enable/site/bi/db2rules.html

DB2 for IBM i - Articles and White Papers

AS400 Articles and White Papers

A new approach to enforcing business rules with DB2 for IBM i

Accessing DB2 for i5/OS data from a Linux Partition and Optimize Linux access to your DB2 for i5/OS database

Accessing DB2 for i5/OS data from AIX Applications - Optimize AIX access to your DB2 for i5/OS database

Building Web services with db2 and .Net

Creating and using materialized query tables (MQT) in IBM DB2 for i5/OS

IBM DB2 Development Add-in for Microsoft Visual Studio .NET

DB2 for i5/OS and Web Services

Debugging SQL procedures with the System i graphical debugger and DB2 developers workbench

Heterogeneous Data Access for iSeries Applications

IBM DB2 for i5/OS and Development Center

Improving SQL procedure performance: New features and tips to help improve stored-procedure performance
Indexing and statistics strategies for DB2 for i5/OS
Modernizing a DB2 for i5/OS application
Protecting i5/OS data with encryption [New]
QIBM_QDB_OPEN: The Open Database File-exit program - Using the Open Database File-exit program to secure your database [New]
Routing database client connections to IBM DB2 for i5/OS through an alternate subsystem
Star Schema Join Support within DB2 for i5/OS - Version 3
Table Partitioning Strategies for DB2 for i5/OS
Understanding the DB2 Universal Database on iSeries systems
Using DB2 Connect in an iSeries Linux Partition - Optimize the DB2 Connect access to a DB2 UDB for iSeries from a Linux partition white paper
Using IBM Rational Application Developer Version 6.0 wizards
Using iSeries database monitor to identify and tune SQL queries
Using iSeries ODBC Driver in a Linux Partition - Optimize the access to your DB2 UDB for iSeries database from a Linux partition
IBM DB2 for as400 i5/OS and DB2 Developer Workbench

Use as400 SQL views to transfer packed data using FTP

The question of how to transfer packed data from an iSeries to an ASCII system has been asked many times. It has been suggested that one can use a logical file that redefines the packed field as zoned. This works as long as the value is non-negative and the number of decimals is 0 - the decimal point does not get transferred. CPYTOIMPF and CPYFRMIMPF can also be used but require extra steps in the process.

A standard logical file cannot convert numeric data to character, either. But an SQL logical view can use the SQL CHAR function to convert any numeric data to left-justified values that have the sign and the decimal point

http://search400.techtarget.com/tip/1,289483,sid3_gci1044575,00.html