Playing around with LOB objects

As part of the new features we are adding to the HA4i product we needed to build a test bed to make sure the LOB processing we had developed would actually work. I have to admit I was totally in the dark when it comes to LOB fields in a database! So we had a lot of reading and learning to do before we could successfully test the replication process.

The first challenge was using SQL, we have used SQL in PHP for a number of years but to be honest the complexity we got into was very minimal. For this test we needed to be able to build SQL tables and then add a number of features which would allow us to test the reproduction of the changes on one system to the other. Even now I think we have only scratched the surface of what SQL can do for you as opposed to the standard DDS files we have been creating for years!

To start off with we spent a fair amount of time trawling through the IBM manuals and redbooks looking for information on how we needed to process LOB’s. The manuals were probably the best source of information but the redbooks did give a couple of examples which we took advantage of. The next thing we needed was a sample database to work with (if we swing between catalogues, libraries, tables, files too often we are sorry!) which would give us a base to start from. Luckily IBM has a nice database they ship with the OS that we use could for this very purpose, it had most of the features we wanted to test plus a lot more we did not even know about. To build the database IBM provides a stored procedure (CALL QSYS.CREATE_SQL_SAMPLE (‘SAMPLE’)), we ran the request in Navigator for i (not sure what they call it now) using the SQL Scripts capabilities and changed the parameter to ‘CORPDATA’. This created a very nice sample database for us to play with.

We removed the QSQJRN set up as we do not like data objects to be in the same library as the journal and then created a new journal environment. We started journaling of all the files to the new journal and added a remote journal. One feature we take advantage of is the ability to start journaling against a library which ensure any new files created in the library are picked up and replicated to the target. The whole setup was then replicated on the target system and configured into HA4i.

As we were particularly interested in LOBs and did not want to make too many changes to the sample database we decided to create our own tables in the same library. The new files we created used the following SQL statements.

CREATE TABLE corpdata/testdta
(First_Col varchar(10240),
Text_Obj CLOB(10K),
Bin_Obj BLOB(20M),
Forth_Col varchar(1024),
Fifth_Col varchar(1024),
tstamp_column TIMESTAMP NOT NULL FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP)
CREATE TABLE corpdata/manuals
(Description varchar(10240),
Text_Obj CLOB(10K),
Bin_Obj BLOB(1M),
tstamp_column TIMESTAMP NOT NULL FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP)

We will discuss the tstamp_column fields later as these are important to understand from a replication perspective. We checked the target and HA4i had successfully created the new objects for us so we could now move onto adding some data into the files.

Because we have LOB fields we cannot use the UPDDTA option we have become so fond of, so we needed to create a program that would add the required data into the file. After some digging around we found that C can be used for this purpose (luckily as we are C programmers) and set about developing a simple program (yes it is very simple) to add the data to the file. Here is the SIMPLE program we came up with which is based on the samples supplied by IBM in the manuals.


#include
#include
#include
#include

EXEC SQL INCLUDE SQLCA;

int main(int argc, char **argv) {
FILE *qprint;

EXEC SQL BEGIN DECLARE SECTION;
SQL TYPE IS BLOB_FILE bin_file;
SQL TYPE IS CLOB_FILE txt_file;
EXEC SQL END DECLARE SECTION;

EXEC SQL WHENEVER SQLERROR GO TO badnews;

qprint=fopen("QPRINT","w");
/* set up the link */
strcpy(bin_file.name,argv[1]);
strcpy(txt_file.name,argv[2]);
/* length of the file names */
txt_file.name_length = strlen(txt_file.name);
bin_file.name_length = strlen(bin_file.name);
/* SQL Option */
txt_file.file_options = SQL_FILE_READ;
bin_file.file_options = SQL_FILE_READ;

EXEC SQL

INSERT INTO CORPDATA/TESTDTA
VALUES ('Another test of the insert routine into CLOB-BLOB Columns',
:txt_file,
:bin_file,
'Text in the next column',
'This is the text in the last column of the table....',
DEFAULT);

EXEC SQL COMMIT WORK;
goto finished;

badnews:

fprintf(qprint,"There seems to have been an error in the SQL?\n"
"SQLCODE = %5d\n",SQLCODE);

finished:
fclose(qprint);

exit(0);
}

The program takes 2 strings which are the paths to the CLOB and BLOB objects we want installed into the table. This program is for updating the TESTDTA table, but is only slightly different to the program required to add records to the MANUALS table. As I said it is very simple, but for our test purposes it does the job..

Once we had compiled the programs we then called the program to add the data, it doesn’t matter how many times we called it with the same data so a simple CL script in a loop allowed us to generate a number of entries at a time. The :txt_file and :bin file are references to the objects we would be writing to the tables, the manuals have a very good explanation on what these are and why they are useful.

Once we had run the program a few times we found the data had been successfully added to the file. The LOB data however, does not show up in a DSPPFM but is instead represented by *POINTER in the output as can be seen below.

Here is the DSPPFM output which relates to the LOB/CLOB Fields.

…+….5….+….6….+….7….+….8….+….9….+….0.
*POINTER *POINTER

The same thing goes for the Journal entry.

Column *…+….1….+….2….+….3….+….4….+….5
10201 ‘ ‘
10251 ‘ *POINTER *POINTER ‘

We have an audit program which we ran against the table on each system to confirm the record content is the same, this came back positive so it looks like the add function works as designed!

The next requirement was to be able to update the file, this can be accomplished with SQL from the interactive SQL screens which is how we ecided to make the updates. Here is a sample of the updates used against one of the files which updates the record found at rrn 3.

UPDATE CORPDATA/MANUALS SET DESCRIPTION =
'This updates the character field in the file after reusedlt changed to *no in file open2'
WHERE RRN(manuals) = 3

Again we audited the data on each system and confirmed that the updates had been successfully replicated to the target system.

That was it, the basic tests we ran confirmed we could replicate the creation and update of the SQL tables which had LOB content. We also built a number of other tests checked that the ALTER table and add of new views etc would work but for the LOB testing this showed us that the replication tool HA4i could manage the add, update and delete of records which contained LOB data.

I have to say we had a lot of hair pulling and head scratching when it came to the actual replication process programming, especially with the limited information IBM provides. But we prevailed and the replication appears to be working just fine.

This is where I point out one company who is hoping to make everyone sit up and listen even though it is nothing to do with High Availability Solutions. Tembo Technologies of South Africa has a product which we were looking at initially to help companies modernize their databases, moving from the old DDS based file system to a new DDL based file system. Now that I have been playing with the LOB support and seen some of the other VERY neat features SQL offers above and beyond the old DDS technology I am convinced they have something everyone should be considering. Even if you just make the initial change and convert your existing DDS based files into DDL the benefits will be enormous once you start to move to the next stage of application modernization. Unless you modernize your database the application you have today will be constrained by the DDS technology. SQL programming is definitely something we will be learning more about in the future.

As always, we continue to develop new features and functionality for HA4i and its sister product JGQ4i. We hope you find the information we provide useful and take the opportunity to look at our products for your High Availability needs.

Chris…

Article Global Facebook Twitter Myspace Friendfeed Technorati del.icio.us Digg Google StumbleUpon Eli Pets

This entry was posted in Application Modernization, C Programming System i5, Disaster recovery, High Availability, Main Category, Personal thoughts by Chris Hird. Bookmark the permalink.

About Chris Hird

Chris Hird President and Owner Shield Advanced Solutions. Hird first worked with high availability at IBM Havant in the United Kingdom in 1989, and he was responsible for the technical interface with the developers of HA products and setting up a support structure in the UK to support the IBM customers. He left IBM to set up Shield Software Services in 1993, which was an IBM business partner and a MiMiX reseller, and in 1997 he moved to Canada and launched Shield Advanced Solutions, which provides tools and utilities aimed mainly at supporting HA Environments. Programs in C and PHP on the i5.

Leave a Reply