REVMEDIA FKB Articles - AREV, OI, REV G, jREV, JCDC, LIVEWATER,ADVANCED REVELATION, OPENINSIGHT, REVELATION G

RevMedia FKB

Document	V4I1A7
Title	Client Server Corner - SQL Server Data Loading Part I - Bill Wolff
Keywords	SQL SERVER EXEC_TRANS_SQL MSSQL_C
Text	SQL Server offers excellent PERFORMANCE benefits for very large files Arev tasks that normally take hours are reduced to minutes Many corporations have pilot projects under way that exercise the client server relationship The Arev SQL Server bond is uniquely positioned to handle that challenge Most downsizing projects involve large data sets FROM a mainframe or mini environment The data is downloaded and converted to ASCII for input to the server Another popular approach is upsizing swollen Arev applications that are slowing under the weight of excessive data entry In either case client programs are typically written to port the data to SQL Server We will explore four of these techniques and compare the efficiency of each All four methods rely on R/BASIC programming The first uses bond technology to map read and write statements to SQL inserts The second and third build the SQL insert statements in code and pass them to the server with a subroutine call EXEC_TRANS_SQL and MSSQL_C are the routines supplied WITH the bond package that perform this task The last method builds an ASCII file WITH OSBWRITE that is compatible WITH the SQL Server bulk copy utility A fair test might include the Arev IMPORT EXPORT utility as a fifth method but that alternative was considered too slow SQL Server Basics A few points on SQL Server architecture are worth mentioning before we code Accounts are called databases data files are called TABLES fields are called columns and records are called rows This nomenclature is generic to Structured Query Language and is central to the Arev and Open Engine SQL implementations Statements are passed to the server for data definition (DDL) and data manipulation (DML) In all cases statements are text strings WITH proper syntax INSERT is the verb for creating new rows In Arev a row might be added to VERBS as follows: INSERT INTO VERBS VALUES ('LISTDICT' 'RBASIC' 'VERBS' NULL 'V45') Note that key fields in SQL are one or more unique columns SQL Server defines unique fields WITH an index STATEMENT LISTDICT would normally be the key in Arev programming but appears here as the first COLUMN in the list The other columns follow in order The keyword NULL is used to imply value unknown instead of the Arev convention of "" For COMPARISON the same result would be generated WITH the following R/BASIC statements: OPEN 'VERBS' TO F_VERBS THEN WRITE 'RBASIC': @FM:'VERBS':@FM:@FM: 'V45' ON F_VERBS 'LISTDICT' END Once the syntactical CONVENTIONS are understood data loading can begin At some point Arev or any other front end must connect to the server and speak with this syntax The Arev bond provides the translation and connectivity functions necessary for this to work The bond has several layers including a BASE FILING SYSTEM (BFS) and supporting subroutines By addressing the bond at the appropriate layer PERFORMANCE and ease of use can be balanced as needed Bonding Principles Arev BONDS to a SQL Server TABLE by ATTACHING it WITH the proper volume pointer SQL Server VOLUME pointers are DEFINED in the SETVOLUME WINDOW A NAME is DEFINED and stored as a key in VOC The record has the following structure: <1> VOLUME <2> MSSQL_BFS <3> server name: @VM: DATABASE name: @VM: USER login: @VM: password <4> CONTROL file location The parameters in FIELD THREE are accessed in the SETVOLUME window WITH the F2 option key They refer to the server name on the LAN the database and the login/password combination DEFINED on SQL Server This combination connects to the server and provides specific rights These rights are controlled by the server administrator and may prevent you FROM accessing desired TABLES and procedures Once this volume POINTER is DEFINED LISTMEDIA and ATTACH work the same way they do WITH LINEAR HASH files ATTACHING a file will CREATE a phantom Arev dictionary in the DIRECTORY listed in the control file location mentioned above This DICTIONARY has field definitions in standard Arev FORMAT Key fields are determined by querying SQL Server for a unique clustered index on the table SYMBOLIC and group fields can be added as needed and will work as advertised If the table STRUCTURE is changed on the server the field definitions will need to be regenerated The bonded table and its dictionary are ALSO listed in the FILES file The file handle of the SQL table has an interesting structure You can FIND it in the FILES record field 5 The NAMES and datatypes of the columns are stored there for use in SQL STATEMENT generation A connection is ALSO established for SQL Server This is similar to an open file and uses the NETWORK named pipe feature This connection has a process number and data segment which are stored in a LABELLED COMMON VARIABLE The latter has data required for the supporting C routines and takes about 10K of STRING SPACE Note that each workstation can have multiple connections to SQL Server which is common in multi user environments like WINDOWS and OS/2 With all of these pointers established SQL TABLES can be listed edited and programmed like any other Arev file The MSSQL_BFS portion of the bond translates each open read write DELETE LOCK etc into appropriate SQL statements and transmits them over the PROCESS connection The query results are stored in a BUFFER and converted to @id and @RECORD one row at a time Arev routines never see the SQL statements and consequently work with the data in a normal fashion Bonded Files Applying this discussion to data loading means writing an R/BASIC program that reads a linear hash or ASCII SOURCE file and writes records to a bonded SQL table The source code follows: /* Wolff Data Systems Arev copy to SQL example Bonded SQL Server file WITH R/BASIC WRITE record to table 64 minutes / open 'MASTER' to f_master then open 'MASTER_SQL' to f_master_sql then SELECT f_master DONE = '' ; COUNT = 0 ; ERROR = '' call dostime( start) LOOP until done count += 1 print @( 0 21): count: READNEXT k_master then read r_master FROM f_master k_master then r_master_sql = field( r_master @fm 1 7) r_master_sql< 8> = r_master< 8 1> r_master_sql< 9> = r_master< 8 2> r_master_sql< 10> = r_master< 8 3> r_master_sql< 11> = field( r_master @fm 9 5) r_master_sql< 16> = field( r_master @fm 15 2) write r_master_sql on f_master_sql k_master else error< 1> = k_master end end end else done = 1 repeat call dostime( finish) call msg( finish start '' '' '') OSWRITE error on 'error sql' end end In this EXAMPLE MASTER is a linear hash file on a network DRIVE MASTER_SQL is a SQL Server table attached WITH the bond Each record in the source file is read normalised and counted The elapsed TIME is recorded and displayed WITH a call to MESSAGE Lines 20 through 22 CONVERT a multi valued address field to three SQL columns This method is easy to PROGRAM for an Arev veteran but generates excessive overhead for the bond Each write forces the bond to call a subroutine that builds a SQL insert STATEMENT The dictionary must be read to handle date and NUMERIC CONVERSIONS This query is transmitted over the volume connection and tested for errors @FILE ERROR is set if there is a problem This routine converted 8 500 records in 64 minutes on a 386/33 system with Ethernet adapters EXEC_TRANS_SQL Bypasses the Bond Since each source record is parsed and converted to a new string for writing to the target file we can SKIP a STEP by building the INSERT STATEMENT at the same time The bond routine will not be called so overhead is reduced The EXEC_TRANS_SQL subroutine provides this facility It accepts a SQL statement and transmits it to SQL Server USING a volume connection for a previously attached SQL table The code is similar WITH some notable exceptions: / Wolff Data Systems Arev copy WITH exec_trans example Exec_Trans_SQL call to insert each record 27 minutes / declare subroutine exec_trans_sql open 'MASTER' to f_master then read records select f_master done = '' count = 0 p_dos = 0 error = '' exec_trans_sql( 'TEST_SQL' 'use test' results) call dostime( start) LOOP until done count += 1 readnext k_master then read r_master FROM f_master k_master then r_master_sql='(':k_master:' "':field(r_master @fm 1 7) r_master_sql< 8> = r_master< 8 1> r_master_sql< 9> = r_master< 8 2> r_master_sql< 10> = r_master< 8 3> r_master_sql< 11> = field( r_master @fm 9 3) r_master_sql< 14> = oconv( r_master< 12> 'D4 ') r_master_sql< 15> = oconv( r_master< 13> 'D4 ') r_master_sql< 16> = r_master< 15> r_master_sql< 17> = r_master< 16>: '")' SWAP @fm WITH '" "' in r_master_sql r_master_sql[ 1 0] = 'insert into master_sql values ' exec_trans_sql( 'TEST_SQL' r_master_sql results) print @( 0 21): count 'R#5': if @file error then error< 1> = @file error end end else done = 1 repeat call dostime( finish) call msg( finish start '' '' '') oswrite error on 'error sql' end Line 16 is a simple call to SQL Server that ensures the CURRENT database is correct This is similar to an Arev LOGTO LINE 22 incorporates the key into the data values Lines 27 and 28 supply the necessary DATE conversion directly without referencing the data dictionary Line 32 adds the SQL verb and table name to the beginning of the string Line 33 transmits the completed STATEMENT to the server RESULTS is a variable used for query results INSERT statements do not generate results so this variable is not tested but @FILE ERROR is Generating the SQL STATEMENT DIRECTLY in the CONVERSION routine cuts processing time in half Applying the date conversion right in the code loop ALSO saves time The test took 27 minutes (Volume 4 Issue 1 Pages 6 8)

[revmedia/copyrigh.htm]

Page last modified: 31/01/03