Get the most out of your Robelle Tools
SQL Server and TurboIMAGE Data
By Bob Green
Last month we explored the process of transforming TurboIMAGE data into formats that are acceptable to Oracle. If you havent read that article, this would be a good time to read it, since it covers the basics of MPE/IMAGE datatypes, which are important regardless of why you are transforming your data. You can read it at www.robelle.com/tips/oracle-datatypes.html. The datatypes in Microsoft SQL Server look a lot like Oracle datatypes.
For character data, the most common datatype is VARCHAR(n) with a specified maximum number of characters. If all data values will be the same length, you can use CHAR(n) instead. If you want to use UNICODE values (i.e., characters beyond the 256 coded in ANSI such as appear in non-English languages), you will use NCHAR(n) and NVARCHAR(n).
For integer values without a decimal place, you can use Integer datatypes:
TINYINT = 8 bits, 1 byte, unsigned, values 0 to 255.
SMALLINT = 16 bits, 2 bytes, signed, values 32,768 to +32,767. Same as I1 in TurboIMAGE.
INT or INTEGER = 32 bits, 4 bytes, signed, values 2B to + 2B. Same as I2 in TurboIMAGE.
BIGINT = 64 bits, 8 bytes, signed integer, aka QUAD; for very large values, up to 18 digits. This was introduced in SQL Server 2000 and is the same as I4 in TurboIMAGE. Before you select this datatype, ensure that COBOL supports it on your platform.
NUM or NUMBER or DEC = numbers with decimal places. You specify a precision and scale for the values. Precision is the maximum total digits in the values, with 38 the largest allowed by SQL Sever. Scale is the number of places to the right of the decimal. The maximum number of digits that can be placed to the left of the decimal is precision-scale. For example, DEC(7,2) means the same as S9(5)V9(2) in COBOL. NUMERIC or FLOAT is the datatype for any value with a decimal place. NUMERIC in SQL Server is much like NUMERIC in Oracle, although it does not have the odd negative scale factors of Oracle (scale factor-3 in Oracle actually multiplies the value by 1000!).
FLOAT(n) = approximate numeric values in floating point format. Supported in 4 byte and 8 byte formats. A floating point number has an exponent and a mantissa. FLOAT(n) specifies number of bits for the mantissa, which can be up to 53. 1 through 24 specify a single precision real (4 bytes) and 25 through 53 specify Double Precision (8 bytes). Same as e2 and e4 in TurboIMAGE.
Other SQL Server datatypes that you will find useful are MONEY and DATETIME.
SQL Tables and TurboIMAGE Datasets
Take a look at Figure 1 below. In the figure, suppose you have a CUSTOMER table in your SQL Server database, and you have a M-CUSTOMER dataset in your TurboIMAGE database. (All of the X fields in TurboIMAGE have been converted to VARCHAR fields.)
CUSTOMER Table Data Column Name Datatype Nullable CustomerID INT No FirstName VARCHAR(10) No LastName VARCHAR(16) No Address1 VARCHAR(26) No Address2 VARCHAR(26) No City VARCHAR(12) No State VARCHAR(2) No ZipCode VARCHAR(16) No CreditRating DECIMAL(9,2) No CustomerStatus VARCHAR(2) No M-CUSTOMER Master Set# 1 Entry: Offset CITY X12 1 CREDIT-RATING J2 13 s9(7)V9(2) CUST-ACCOUNT Z8 17 Search Field CUST-STATUS X2 25 NAME-FIRST X10 27 NAME-LAST X16 37 STATE-CODE X2 53 STREET-ADDRESS 2X25 55 ZIP-CODE X6 105 Capacity: 211 (7) Entries: 12 Bytes: 110
The CUST-ACCOUNT number field in TurboIMAGE was a Z8 field to enforce better hashing of the values, but Z indicates that it always contains numeric values. Therefore, INT is an appropriate SQL Server datatype for CustomerID.
The CREDIT-RATING field is a 32-bit integer on the HP 3000, defined with two decimal places in the COBOL programs. Since SQL Server has a NUMERIC datatype that is aware of decimal places, it is more appropriate to use that datatype than INT.
The repeated item STREET-ADDRESS (2X25) has been converted into ADDRESS1 and ADDRESS2 in SQL, since SQL does not have repeated data items.
The ZIPCODE field has been expanded from X(6) to VARCHAR (16) to handle extended and foreign postal codes.
Moving TurboIMAGE Data to SQL Server
There are three ways to move your TurboIMAGE data to SQL Server:
Export your data to a file, then use the BULK INSERT statement in SQL.
Export your data to a file, then use the BCP command-line utility (BCP stands for Bulk Copy Program).
Export your TurboIMAGE data to a file or create an ODBC link to it, then use Data Transformation Services (DTS).
BULK INSERT and BCP are very similar in their parameters and options, but BULK INSERT is faster because it doesnt go through as many layers of network code. DTS is a much more sophisticated service, including a wizard to help define your import or export transaction.
The default SQL Server import file expects all field values to be in character format, then uses Tab as the field terminator and \newline as the row terminator.
Use Suprtool For SQL Server Import
Suprtool from Robelle has all the options needed to generate an import file that is compatible with SQL Server.
For example, to export the contents of the M-CUSTOMER dataset to the CUSTOMER table, you would use the following steps:
On the HP 3000, SUPRTOOL extracts the fields in the order they appear in the SQL table (or you could use a FORMAT file with SQL Server to do the reordering, but SUPRTOOL EXTRACT seems simpler to me):
Extract city, state-code,zip-code,credit-rating,cust-status
This Suprtool task gets the column values out of the dataset and puts them in the proper order for the SQL table, but they are still in the native HP 3000 format. They still need to be converted to ASCII characters and have Tab terminators inserted. This is done using SUPRTOOLs STExport utility. Here are the commands to take the self-describing file (SDFILE) created by SUPRTOOL and convert it into the file that SQL Server expects:
Input sdfile (created above by Suprtool)
By default, STExport creates a variable length record file for output. This is just what we need to copy to the Windows server.
(Note: Although there are no date fields in this table, I included the suggested date format, YYYYMMDD, since this format is always recognized by SQL Server.)
Use FTP to transfer the STExport output file to your SQL Server system, but remember to do it as an ASCII transfer, not a BINARY transfer. This is so that the \newline characters are translated properly at the end of each row.
user admin passwd
put sqls01 sqls01.txt
On the Windows Server, you can use BCP or BULK INSERT to insert the rows into the CUSTOMER table. In BCP you select the character option (-c):
bcp custdb..customer in sqls01.txt c
S servername U userid P password
With BULK INSERT, you want to select char as the file type:
BULK INSERT custdb..customer from c:\sqls01.txt
If the file from the HP 3000 does not have the columns in the order of the SQL Table, or you need to skip some fields, then you need to create a Format File. This is beyond the scope of this article, but is described well in the SQL Server user documentation.
With Oracle, the package comes with pre-processor for Fortran and COBOL, but SQL Server seems to only come with a pre-processor for C++.
What if you want to do SQL Server functions in your COBOL program? You must look to your compiler vendor.
For example, AcuSQL has an Embedded SQL (ESQL) precompiler that lets you embed standard SQL directly into ACUCOBOL program. Theres more information at www.acucorp.com/Solutions/access3.html and www.acucorp.com/Solutions/acusql.html
I looked for a FORTRAN precompiler for SQL Server, but did not find one, so that problem is left to the reader. I am including some resources on SQL Server to help you with your migration.
SQL Server Resources
Inside Microsoft SQL Server 2000, Kalen Delany. Advanced, internal structures.
SQL Server books online
Microsoft Web site: www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
SQL Team news and
forum site: www.sqlteam.com
SQL Server Resources: www.sqldts.com/dblinks.asp?nav=1,2,30,1
SQL City: www.mssqlcity.com
DTS Web site: www.sqldts.com
SQL Web sites: www.mssqlcity.com/Links.htm