(Continued from page 1)

The first task is to define a new database on the Windows box, using the SQL Enterprise Manager. Next, you can run TIPS, which will generate a SQL Server schema generation file which for your HP3000 TurboImage, KSAM and flat files. Transfer the schema generation file, created by TIPS, to your Windows machine, and execute it against your new empty database, using the Query Analyzer. You have now replicated your HP3000 database structures in an empty MS SQL database.

Building the data transfer function:

The next step is to build and schedule a job on the HP3000 which has the following steps:

1) Execute TIPS to extract the data, and the "bulk load script file"

2) Transfer the data files, and the "bulk load script file" to your Windows machine using ftp.

3) Transfer a trigger file to your Windows machine using ftp.

Next, you construct a .bat file on your Windows machine, whose purpose is to look for the trigger file, then load the data files as sent by the HP3000, to the SQL Server tables, using the "bulk load script file" built by TIPS.This .bat file typically look something like this:

if exists C:\comp3\triggerfile go to runload
echo **************************************
echo * trigger file missing. Cannot load to SQL Server
echo **************************************
goto end
:runload
isql -Usa -P -dSalesDB -itabloads -oloaddb.log
del C:\comp3\triggerfile
:end

The isql statement seen above is a dos command which can execute SQL Server commands, in this case the contents of the bulk load script file.The ISQL syntax is:

isql -Usa -Psa -dSalesDB -itabloads -oloaddb.log

Where sa (following -U) is your SQL Server user name, sa (following -P) is the password for this user (this may be left blank, but you still need the -P option) , Salesdb (following -d) is your SQL Server database name, and tabloads (following -i) is the input file, as created by TIPS and ftp'ed to the Windows system.

Whatever tools you employ, it's a good idea to use MS SQL's bulk load facility to load your data, as this method is orders of magnitude faster than any load method which uses cursors.

To refine this methodology, you would likely only send a subset of your HP3000 data to Windows for general report usage, not only to prevent users from seeing sensitive data, but also to decrease the load time. Various features in TIPS allow you to select a subset of data to load to SQL Server. The SKIP command can be used to skip files, datasets or fields. In addition, a user exit allows you examine each record, and decide whether to extract it or not. Using this, you can only changed data to the Windows data store.

As a final point, you should strive to load your HP3000 data fields to SQL Server as native data types, not just character strings and integers. For example, dates should be loaded as type 'datetime', currency values as type 'money', fields which use COBOL implied decimal (e.g., S(7)V99) should be sent with the correct number of decimal positions, etc.

Remember that in the Windows environment, you will be running tools which can automatically interpret and format based on these data types, and also you have very powerful SQL Server functions for doing date calculations.

TIPS wildcard conversion features allows you to easily convert to the correct data types. For example, consider the following Tips CONFIG file example:

ITEM @.@DATE@ SQLTYPE datetime
ITEM @.UPDATE-FLAG SQLTYPE varchar(2)

These two commands, collectively, direct TIPS to convert all HP3000 data whose name contains the string 'DATE' to a SQL Server datetime field, EXCEPT FOR the field UPDATE-FLAG (in any dataset) , which will go over as as a character field.

Another CONFIG file example:

DEFAULTNUMERIC SQLTYPE=money NUMIMPLIED=2
ITEM @.ORDER-QTY SQLTYPE=integer NUMIMPLIED=0

These two commands direct TIPS to convert all your non-real numeric data fields (types P,I,J,Z,J) to SQL Server money types, and insert two decimal points, EXCEPT for item ORDER-QTY (in any dataset), which will be converted to integer, with no decimal places.

Using these methods, you should be able to quickly set up an automatically refreshed SQL Server copy of your HP3000 data. After you have "proof of concept" and users have access to the data on Windows, you may be able to continue using your HP3000 as your business server for years to come.

(Return to page 1)

Back to top