Oct 19

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…

Oct 12

HA4i and IFS Journal entry replay

One of the features we have been working on recently is the ability to replay journal entries generated by IFS activity. The HA4i product does support the replay of IFS journal entries but only via the *IBM (APYJRNCHG) apply process. Having seen the improvements we had gained by implementing our own apply process for DB journal entries we decided that we needed to offer the same capabilities with the IFS journal entries.

The technology for applying updates to IFS objects using the journal entries had been partially developed for sometime, however development stopped when we found that the JID and Object ID could not be maintained between systems using IBM API’s. You might ask why? Well because some of the journal entries deposited into the journal do not have the path to the object and only the Object ID we needed some method to extract the true path to the object from the content of the journal entry. As the JID and Object ID are different we could not use the API’s that are available to convert those ID’s into the path. We did ask IBM if they would provide a solution in much the same manner as they do for Database file (QDBRPLAY) and Data Area – Data Queue (QjoReplayJournalEntry) which protect the JID of the created object, this in turn allows us to use the API’s to extract the actual Path of the object using the JID contained in the journal entry. But they said it could not be done (they already have to do it for the APYJRNCHG but would not expose it to others) and suggested we came up with a table or other technology which would allow us to track each and every IFS object, we thought that would be a nightmare to handle especially as one of our clients had to split his IFS over 3 journals just because he hit the maximum number of objects which can be journaled to a single journal! Still, when push came to shove we bit the bullet and built a technology to track the IFS objects which would then allow us to manipulate the IFS objects using the journal entries.

We faced a number of challenges with the replication technology such as security and CCSID conversion, but eventually we got to the bottom of the pile and the apply of IFS generated updates now works. We are still surprised people use the IFS especially with the abundance of better storage solutions out there, but we can now provide our own apply process for the IFS journal entries. Tracking of the JID and Object ID is now carried out very effectively without the use of a DB table and it is very fast and has a very low CPU impact.

We are not finished yet though, we are now working on implementing support for some of the more obscure DB2 capabilities plus the Journal Minimal Data option with *FLDBDY and *FILE support. We are also experimenting with Identity columns and User Defined Types to name a few. You may not use these capabilities now but having built the tests to allow us to test them within HA4i, I must admit I am going to use them a lot more in the future.

HA4i continues to improve as a solid High Availability solution, having already built a lot of new features in the latest Version(7.1) we now have another set of features ready for release in the next PTF. If you are looking at HA or want to reduce the cost of your current implementation give us a call, we may surprise you with what we can offer. We might be small but that does not stop us from developing first class solutions and at a cost you can afford.

Chris…

Oct 01

HA4i running in production and performance is better than expected

HA4i Version 7.1 was announced a few weeks ago now and we have been upgrading the current HA4i V6R1 customers to the latest version. After a few initial teething problems we are seeing a big improvement in the performance of the new apply process over the existing APYJRNCHG process. The main reason for this is the lack of locking and unlocking of files every time the receiver changes, with the APYJRNCHG command we would see all of the files locked before any journal entries would be applied. In most customers this is not a problem, but in those where there are thousands of files defined to a single journal it could cause some delays while all the files are locked prior to the updates being applied. This was in fact one of the major reasons we decided to create our own apply process so we could control just what was locked and when especially as IBM was unwilling to change any of their processes which we relied on.

The new apply process now locks a file only when it has updates to apply, this has provided us with tremendous catchup capabilities because it is only opening files as updates are seen. In one case the client had over 30,000 files defined to a single journal, with the new apply the most files we have ever seen open is just over 200, this means the journal has over 29,800 files which are never updated yet would have required locking each and every time the IBM apply process was run.

The new apply process is providing many more benefits than we first expected and we are continuing to improve its performance and capabilities. if you are looking at High Availability HA4i should be on your list of solutions to consider.

Chris…