Data Build Manual

ManualsBrandsHP ManualsServerHP NonStop G-Series

Data Management Library

Tandem

Data Build Manual

Abstract This manual describes how to use Tandem Data Build, a data-conversion utility, to convert

data that is not Tandem data to Tandem database structures and load the data to a NonStop

SQL or Enscribe database. This manual also describes how to load data from a flat file or

Tandem Enscribe file to a NonStop SQL database.

Part Number 099331

Edition Second

Published September 1993

Product Version D20

Release ID D20.00

Supported Releases This manual supports C30.00 and D20.00 and all subsequent releases until otherwise

indicated in a new edition.

Summary of content (210 pages)

PAGE 1
Data Management Library Tandem Data Build Manual Abstract This manual describes how to use Tandem Data Build, a data-conversion utility, to convert data that is not Tandem data to Tandem database structures and load the data to a NonStop SQL or Enscribe database. This manual also describes how to load data from a flat file or Tandem Enscribe file to a NonStop SQL database. Part Number 099331 Edition Second Published Product Version Release ID Supported Releases September 1993 D20 D20.
PAGE 2
Document History Edition Part Number Product Version Earliest Supported Release Published First Second 081313 099331 C30 D20 C30.08 D20.00 July 1992 September 1993 New editions incorporate any updates issued since the previous edition. A plus sign (+) after a release ID indicates that this manual describes function added to the base release, either by an interim product modification (IPM) or by a new product version on a .99 site update tape (SUT).
PAGE 3
New and Changed Information New Information The manual has been streamlined and reorganized so that information is easier to find.
PAGE 4
New and Changed Information Changed Information Changed Information The following table summarizes the minor changes made to the product since the last release of the manual. Change Description Section Tape DEFINE A tape DEFINE is included in the DEFINES and PATHCNFG files. Data Build now displays each tape input error, and you can control how many errors to tolerate before ending. Data Build purges all files on an existing project subvolume when you run Project. In the C30.
PAGE 5
Contents About This Manual xi Notation Conventions Section 1 xiii Introduction to Data Build How Data Build Works 1-2 Supported and Unsupported Data Types 1-3 Input to Data Build 1-4 Output From Data Build 1-5 System Requirements for C- and D-Series Operating Systems 1-5 C-Series and D-Series Compatibility Issues 1-5 Installing Data Build Using Data Build Section 2 1-6 1-6 Understanding Data Build Files Key Data Build Files 2-1 File and DEFINE Names 2-1 Data Build Fileset 2-1 File Naming Conventio
PAGE 6
Contents Before You Run Project 3-3 Running Project 3-5 Exiting Project 3-8 Checking the Spooler for Errors 3-8 If You Do Not Have NonStop SQL 3-8 Source Record Descriptions File 3-9 Guidelines 3-9 Source Record Descriptions 3-10 Creating a Source Record Descriptions File 3-11 Modifying the DEFINES and PATHCNFG Files Enscribe Conversion 3-13 NonStop SQL Conversion 3-14 Changing PATHCNFG Section 4 3-13 3-15 Running Data Build The Conversion Process 4-1 Checking Your Work 4-4 Determining the Number of
PAGE 7
Contents Section 5 Running the Sample Programs Sample Data Conversion to Enscribe 5-1 Input Files 5-1 Source Record Descriptions 5-1 The DEFINES File 5-2 The PATHCNFG File 5-3 Recap 5-5 Running Data Build 5-5 Exiting Data Build 5-6 Configuring the Conversion Environment 5-6 Building DDL Input 5-9 Generating Conversion Procedures 5-13 Creating the Enscribe Conversion Program 5-17 Running the Enscribe Conversion Program 5-17 Checking the Data 5-18 Where to Go Next 5-18 Sample Data Conversion to NonStop SQ
PAGE 8
Contents User Exits 6-8 Discarding an Input Field 6-10 Discarding Input Records 6-10 Modifying the Generated COBOL85 Conversion Program Customizing SQL-LOAD Output 6-11 SQL-LOAD Process Overview 6-11 How Standard SQLCI Loads Nulls 6-13 Loading SQL Nulls With the SQL-LOAD Option Customizing SQL-LOAD Nulls 6-14 Customizing SQL-LOAD Nulls, Variation 1 6-15 Customizing SQL-LOAD Nulls, Variation 2 6-15 Handling SQL VARCHAR Data 6-17 Sorting Data Before Using SQL-LOAD 6-25 SQL-LOAD Sorted EBCDIC Input 6-25 SQL
PAGE 9
Contents Appendix A Data Build Files Appendix B Sample Conversion Programs Enscribe Conversion Program Appendix C B-1 SQL-LOAD Conversion Program B-18 SQL-INSERT Conversion Program B-39 Conversion Worksheets Project Utility Worksheet C-2 Conversion Environment Worksheet C-3 Set Up Project Environment C-4 Run Data Build C-5 Appendix D Tandem Reserved Words for IXF Input Appendix E Troubleshooting Conversion Environment E-2 Spooler Output E-3 Files Produced or Modified E-4 Glossary Glossar
PAGE 10
Contents Figure 5-2. Conversion Environment for Samples Figure 7-1. Main Menu 5-7 7-1 Figure 7-2. Conversion Environment for Samples Tables Table 1-1. Supported Data Types 7-3 1-3 Table 1-2. Unsupported Data Types 1-3 Table 3-1. Project Utility Worksheet 3-4 Table 3-2. Valid Choices for Source Record Descriptions 3-9 Table 4-1 Conversion Environment Worksheet Table 4-2. OPTIONS Field Settings 4-8 4-10 Table 4-3. Field Conversion to DDL 4-12 Table 7-1.
PAGE 11
About This Manual This manual shows you how to use Tandem Data Build to convert and store data in a Tandem Enscribe or NonStop SQL (Structured Query Language) table. Data Build is a data conversion utility that converts non-Tandem data to Tandem database structures and loads it either to a NonStop SQL database or an Enscribe database. Data Build also loads data from a flat file or a Tandem Enscribe file to a NonStop SQL database in a significantly efficient manner.
PAGE 12
About This Manual Manual Organization Manual Organization This manual has the following sections and appendixes: Section Name Contents 1 Introduction to Data Build Understanding Data Build Files Describes what Data Build is and how to install it.
PAGE 13
Notation Conventions The following list summarizes the conventions for syntax presentation in this manual. Notation Meaning UPPERCASE LETTERS Uppercase letters represent keywords and reserved words; enter these items exactly as shown. Lowercase italic letters represent variable items that you supply. If a space separates two items, that space is required. If one of the items is a punctuation symbol, such as a parenthesis or a comma, spaces are optional.
PAGE 14
1 Introduction to Data Build You use Tandem Data Build to convert data from a non-Tandem system and to load the converted data into a Tandem file. Data Build also loads data from a flat file or a Tandem Enscribe file to a NonStop SQL table. Data Build provides a significant performance enhancement to the standard SQL load process. Data Build creates a COBOL85 conversion program that reads files containing the input data and loads the data to Tandem files.
PAGE 15
Introduction to Data Build How Data Build Works How Data Build Works You use Data Build by pressing function keys F1 through F5 from the Data Build Main Menu, displayed below: T a n d e m D a t a B u i l d DATA CONVERSION FACILITY T9515D20 (01JUN93) -- COPYRIGHT TANDEM COMPUTERS INCORPORATED 1992, 1993 ============================================================================== F1 - CONFIGURE Conversion Environment F2 - BUILD Data Definitions & Run DDL Compiler F3 - GENERATE Conversion Pr
PAGE 16
Introduction to Data Build Supported and Unsupported Data Types Supported and Data Build supports data types that are typically found in business applications. The Unsupported data types not supported by Data Build are those used mainly in scientific and graphic Data Types applications. You can customize Data Build to handle the unsupported data types. Table 1-1 shows the COBOL and DB2 input data types supported by Data Build.
PAGE 17
Introduction to Data Build Input to Data Build Input to Data Build Data Build supports input records that are: On tape or disk file input media Fixed length or (for tape only) variable length In EBCDIC or ASCII Normal data or in Integration Exchange Format (IXF) Comma Separated Values (CSV) on disk (not tape) Note Do not confuse the abbreviation IXF used throughout this manual with the Tandem file transfer utility called IXF.
PAGE 18
Introduction to Data Build System Requirements for C- and D-Series Operating Systems Output From Data Build The type of conversion program that Data Build generates depends on the option you select in Data Build. For example, you can create a conversion program that does one of the following: Data Build Output Option Description Enscribe SQL-LOAD Writes data to entry-sequenced Enscribe files. This is the default. Feeds data to a separate SQLCI process that loads the data to a new NonStop SQL table.
PAGE 19
Introduction to Data Build Installing Data Build Installing Data Build Using Data Build You install the Data Build software files from the Site Update Tape (SUT). The INSTALL program restores the Data Build files from the SUT to the Distribution Subvolume (DSV) and copies them to the Installation Subvolume (ISV) during the installation process. The installation subvolume is named ZDATABLD. Here are the major steps for using Data Build: Complete the Project utility worksheet. Run the Project utility.
PAGE 20
2 Understanding Data Build Files This section describes Key Data Build files Input file sources Key Data Build Files Data Build uses the following key files to convert your data: File Description DDL Record Descriptions File DEFINES File created by Data Build and used to convert your input data to the output data.
PAGE 21
Understanding Data Build Files File and DEFINE Names With the exception of IXF disk input files, the actual input and output file names can be different from the DEFINE names. This can be very confusing. In general, it is better to make the actual file name the same as the DEFINE name. File Naming Conventions You use the Data Build Conversion Environment (F1) screen to specify a prefix name for the input files, Source Record Description files, DDL files, and output files.
PAGE 22
Understanding Data Build Files File and DEFINE Names Non-IXF Disk Input to Enscribe In Figure 2-1, Option 1 is specifies a conversion to Enscribe. Alternatively you could have left it blank as Enscribe is the default. This example shows three input files. Figure 2-1.
PAGE 23
Understanding Data Build Files File and DEFINE Names These files can reside on any subvolume if a qualified subvolume prefix is specified, for example. subvol.DD. The default is normally the Conversion Project Subvolume. Data Files IN The prefix of X plus a suffix specifies a DEFINE name. In this example, the three Enscribe input files have DEFINE names of =X01, =X02 and =X03. For each input file the actual filename may be any valid name on any volume or system.
PAGE 24
Understanding Data Build Files File and DEFINE Names Figure 2-2. Non-IXF Disk Input to SQL-LOAD File-Naming Example Source Record Desc Files: DDL Record Desc Files: -- see info re 1st file -| | V V |-DEFINE =X01,.....,FILE $vol.svolx.aaa IN: X ->| DEFINE =X02,.....,FILE $vol.svoly.bbb |-DEFINE =X03,.....,FILE $vol.svolz.ccc Data name from 01 level in Source Record Descriptions File | | OUT: dummy V DEFINE =ORG,.....,FILE $vol.svola.ab DEFINE =STAFF,...,FILE $vol.svolb.cd DEFINE =PROJ,....,FILE $vol.
PAGE 25
Understanding Data Build Files File and DEFINE Names IXF Disk Input to NonStop SQL In Figure 2-3, Option 1 is set to SQL-LOAD for a conversion to NonStop SQL and Option 3 is set to IXF for IXF input (file naming for SQL-INSERT is the same as for SQL-LOAD). File naming is a little different than for a non-IXF conversion to NonStop SQL as described in the previous subsection. Figure 2-3.
PAGE 26
Understanding Data Build Files File and DEFINE Names For disk input in the BUILD DDL (F2) step, Data Build uses the IN Data Files prefix you specify as follows: Data Build looks for all of the input files on the conversion project subvolume or on another subvolume, if a qualified subvolume prefix, that is subvol.X, was specified. DEFINE names are not used. In this example, Data Build looks for files X01, X02, ... on the conversion project subvolume until it does not find the next Xnn file.
PAGE 27
Understanding Data Build Files File Names in DEFINES and PATHCNFG Files File Names in The actual file or table can be named different than the DEFINE name and each can be DEFINES and on any subvolume except in the following cases: PATHCNFG Files For non-IXF disk input (Enscribe or NonStop SQL output): Data Build checks for every file name with the prefix specified in the Conversion Environment screen ending in a number from 00 to 99.
PAGE 28
Understanding Data Build Files File Names in DEFINES and PATHCNFG Files DEFINES File With Different File Names If you want to use file names that are different from the DEFINE names, you specify this when you tailor the DEFINES file. Here is a DEFINES file that has actual file names different from the DEFINE names. The file names which are different from the DEFINE names are in bold: SET DEFMODE ON DELETE DEFINE =CAT ADD DEFINE= CAT, CLASS CATALOG,SUBVOL $DATA.
PAGE 29
Understanding Data Build Files Tape Input Tape Input The following DEFINE is included in the DEFINES file based on the tape device location you specified when you ran the Project utility: DELETE DEFINE =TAPE ADD DEFINE =TAPE, CLASS TAPE, LABELS BYPASS, DEVICE \system.$device The same DEFINE is also included in the PATHCNFG file as follows: SET SERVER DEFINE =TAPE,CLASS TAPE,LABELS BYPASS,DEVICE \system.$device If the DEFINEs are not correct, correct the DEFINES and PATHCNFG files before you obey XSTART.
PAGE 30
Understanding Data Build Files Tape Input This information is from the QMF V3R1 Reference Manual (SC26-4716). For IBM JCL to create QMF data with DXT, please have your Tandem analyst contact the Data Build Product Manager. Note that IXF data is in variable length format. Data Build supports variable length records only from tape input; variable length records cannot be copied to disk for input to Data Build.
PAGE 31
3 Setting Up the Project Environment The Conversion A Data Build conversion has two major parts: Process Setting Up the Project Environment (done outside of Data Build) Running Data Build (done within Data Build) This section discusses the first part of this conversion process, setting up the project environment. Figure 3-1 shows a flow chart of these steps. An explanation of the steps follows the flow chart.
PAGE 32
Setting Up the Project Environment The Conversion Process Figure 3-1.
PAGE 33
Setting Up the Project Environment Using the Project Utility 1. Define the conversion project. Determine the input files from the source system to convert and get the name of each input file. Use this information to fill out the Project Utility Worksheet, available in Appendix C. 2. Volume to the Data Build install subvolume. 3. Run the Project utility to create a conversion project subvolume. 4. Check the spooler and correct any errors. 5.
PAGE 34
Setting Up the Project Environment Using the Project Utility Likewise, you might add the letter “S” at the end of the above to name your NonStop SQL catalog subvolume the following: $data.ccproj1s Use Table 3-1 to enter your subvolume names and to consider the other parameters you can customize for your project. (You can copy this worksheet, and others that you will need later on, from Appendix C.
PAGE 35
Setting Up the Project Environment Using the Project Utility Running Project To create a conversion project, follow these steps: 1. Volume to the Data Build ISV. For example, enter: 1> volume $data.ZDATABLD 2.
PAGE 36
Setting Up the Project Environment Using the Project Utility 5. Enter the name of a home terminal if you do not want to use the Data Build default. Press RETURN to use the default. The system responds: Enter PATHMON PROCESS NAME If Different -> $DBLD1 > 6. Enter the name of the PATHMON process to be used by Data Build if you do not want to use the default PATHMON process name.
PAGE 37
Setting Up the Project Environment Using the Project Utility YOUR PROJECT SUBVOLUME WILL BE PURGED! If you have an existing project on the project subvolume specified above, all the files on the project subvolume will be purged. This is a change from the C30.08 release of Data Build. In the C30.08 release, Data Build purged only files that had the same names as the new files being created. The system asks for your approval to proceed: Okay To Proceed With This Project? Please Enter Y/N/END... 8.
PAGE 38
Setting Up the Project Environment Using the Project Utility 11. Enter the name, for example: ->$test The system responds: ================================================== A. PROJECT Subvolume -> $DATA.CCPROJ1 B. SQL CATALOG Subvolume -> $DATA.CCPROJ1S C. HOME TERMINAL -> \HQ.$ZTN1.#PTY67 D. PROCESS NAME -> $TEST E. FUP Location -> $SYSTEM.SYS00.FUP F. EDIT Location -> $SYSTEM.SYSTEM.EDIT G. TACL Location -> $SYSTEM.SYS00.TACL H. COBOL Location -> $SYSTEM.SYSTEM.COBOL85 I. DDL Location -> $SYSTEM.SYSTEM.
PAGE 39
Setting Up the Project Environment Source Record Descriptions File Source Record The Source Record Descriptions file is an EDIT file that contains a field-by-field record Descriptions File description of the input data record. It defines the structure of the input data record by specifying the length and data type (for example, character, binary, or packed decimal) of each field in the input record.
PAGE 40
Setting Up the Project Environment Source Record Descriptions File Source Record Descriptions Table 3-2 shows valid options for the source record descriptions of input data types supported by Data Build. Table 1-2 in Section 1, “Introduction to Data Build,” shows data types that are not supported by Data Build. Table 3-2.
PAGE 41
Setting Up the Project Environment Source Record Descriptions File Creating a Source Record Descriptions File You use EDIT or TEDIT to create a Tandem EDIT file for the Source Record Descriptions. To prepare source record descriptions, you can use: Existing COBOL record definitions from a non-Tandem system, modify those definitions, and use the editor to create a Source Record Descriptions file . SQLCI to create source record descriptions for data from an SQL database (such as DB2 on IBM).
PAGE 42
Setting Up the Project Environment Source Record Descriptions File To create the source record descriptions: 1. Execute the SQLCI INVOKE command for each table to produce a Source Record Descriptions file. SQLCI INVOKE tablename FORMAT COBOL85 TO filename CLEAR where tablename is the SQL table just created, and filename is its associated Source Record Descriptions file. SQLCI supplies the following except for the DATE and TIME, which were added by editing the file: 01 DEMO03.
PAGE 43
Setting Up the Project Environment Modifying the DEFINES and PATHCNFG Files Modifying the DEFINES and PATHCNFG Files The DEFINES and PATHCNFG files point to the input files, output files (Enscribe conversion), and the NonStop SQL catalog and tables (NonStop SQL conversion). The DEFINES and PATHCNFG files, which the Project utility copied to the conversion project subvolume, are preconfigured for the sample conversions.
PAGE 44
Setting Up the Project Environment Modifying the DEFINES and PATHCNFG Files NonStop SQL Conversion For a NonStop SQL conversion, the DEFINES file must have a DEFINE for each input file and output table, and the NonStop SQL catalog. The following DEFINEs specify two input files VSAMIN01 and VSAMIN02. Separate DEFINEs name the two NonStop SQL tables STATE and RATETB. The following is an example from a DEFINES file: SET DEFMODE ON DELETE DEFINE =CAT ADD DEFINE =CAT,CLASS CATALOG,SUBVOL $CONVERT.
PAGE 45
Setting Up the Project Environment Changing PATHCNFG Changing PATHCNFG Caution The PATHCNFG file contains settings that are common to most Tandem systems; however, you might need to make some minor changes to it before running Data Build. For example, you might need to change the CPU designations and other Pathway parameters for your system. You should not change this file other than the DEFINES unless you have a working knowledge of Pathway. For more information, see the Pathway Reference Manual.
PAGE 46
Setting Up the Project Environment Changing PATHCNFG RESET SERVER SET SERVER DEFINE =DATAIN01, CLASS MAP, FILE $DATA01.KOD2016.DATAIN01 SET SERVER DEFINE =DATAIN02, CLASS MAP, FILE $DATA01.KOD2016.DATAIN02 SET SERVER DEFINE =DATAIN03, CLASS MAP, FILE $DATA01.KOD2016.DATAIN03 SET SERVER DEFINE =DATAOT01, CLASS MAP, FILE $DATA01.KOD2016.DATAOT01 SET SERVER DEFINE =DATAOT02, CLASS MAP, FILE $DATA01.KOD2016.DATAOT02 SET SERVER DEFINE =DATAOT03, CLASS MAP, FILE $DATA01.KOD2016.
PAGE 47
4 Running Data Build The Conversion A Data Build conversion has two major parts: Process Setting Up the Project Environment (done outside of Data Build) Running Data Build (done within Data Build) This section discusses the second part of this conversion process, running Data Build. Figure 4-1 shows a flow chart of these steps. An explanation of the steps follows the flow chart.
PAGE 48
Running Data Build The Conversion Process Figure 4-1.
PAGE 49
Running Data Build The Conversion Process 1. Complete the Conversion Environment worksheet. 2. Volume to the conversion project subvolume. 3. Obey XSTART to cold start the Pathway system. 4. Obey XRUN to start Data Build and display the Main Menu. Menu items are labeled F1 to F5; each menu item corresponds with a conversion process. Menu items are indicated on the flowchart in Figure 4-1. 5. Press F12 to run Peruse, check the spooler and correct any errors.
PAGE 50
Running Data Build Checking Your Work 23. Press F16 to exit Data Build. If TAPECOM is active ($ZSVR process running) and LABELED_TAPE_SUPPORT ENABLED was specified in SYSGEN, enter the following TAPECOM commands before mounting the input tape: CLEAR NLCHECK CLEAR BLPCHECK Obey XRUN to restart Data Build. 24. Press F5 to run the COBOL85 conversion program to convert the input files.
PAGE 51
Running Data Build Determining the Number of Input Files Determining the The flow chart in Figure 4-2 shows how Data Build determines the number of input Number of Input Files files to convert.
PAGE 52
Running Data Build Running Data Build Running Data Build When you run Data Build, the Main Menu is displayed, as shown in Figure 4-3. Begin working at step F1 and go through each step in succession until you have converted the input files. To start Data Build, volume to the conversion project subvolume and: 1. Cold start the Pathway system by entering: 1> OBEY XSTART The cold start displays on your terminal, and when finished, the TACL prompt returns. 2.
PAGE 53
Running Data Build Configuring the Conversion Environment (F1) Configuring the The Conversion Environment shows the location of the Source Record Descriptions Conversion files, DDL record descriptions files, input files, output files, and system software. This Environment (F1) subsection explains the rules for the Conversion Environment field settings, shows how to specify those settings, and describes each setting in detail.
PAGE 54
Running Data Build Configuring the Conversion Environment (F1) Table 4-1 Conversion Environment Worksheet Field Specify SOURCE Record Descriptions Files (1 to 6 character prefix) DDL Record Descriptions Files (1 to 6 character prefix) DATA Files IN (1 to 6 character prefix) DATA Files OUT (1 to 6 character prefix) (For Enscribe only; for SQL specify “dummy”) Output database OPTION 1 Blank (ENSCRIBE) SQL-LOAD ENSCRIBE SQL-INSERT Input data is (for SQL-LOAD only) OPTION 2 Blank (not SORTED) SORTED I
PAGE 55
Running Data Build Configuring the Conversion Environment (F1) Conversion Environment Fields This subsection describes the purpose of each Conversion Environment field and its acceptable settings. CONVERSION Subvolume Displays the name of the conversion project subvolume for this project. SOURCE Record Descriptions Files Enter the one to six character prefix of the Source Record Descriptions file names.
PAGE 56
Running Data Build Configuring the Conversion Environment (F1) Table 4-2. OPTIONS Field Settings 4–10 Option Number Setting Description 1 blank ENSCRIBE Creates an Enscribe conversion program. 1 SQL-INSERT 1 SQL-LOAD 2 Blank 2 SORTED 3 3 Blank IXF 3 ASCII Creates a NonStop SQL database conversion program for inserting records into NonStop SQL tables. Data Build automatically sets the defaults for the database.
PAGE 57
Running Data Build DDL Input and Output (F2) Location of System Software Names the location of the system software files. You enter these locations at the Project Utility screen. If a location other than EDIT is wrong, do not change it here. Instead, run the Project utility and recreate the data conversion project subvolume with the right settings.
PAGE 58
Running Data Build DDL Input and Output (F2) To run ZZDDLIN manually, do the following: 1. Exit from Data Build (after running the F2 step). 2. Edit and modify the DDL Record Descriptions file. 3. Enter the following at the TACL prompt: DDL /IN zzddlin, OUT $s.#zzddl/ 4. Check spooler location #ZZDDL and correct any errors. 5.
PAGE 59
Running Data Build DDL Input and Output (F2) If the output files have not been created and the input files already exist, follow these steps: 1. Exit Data Build. 2. Edit the FUPDEFS file. 3. Add this statement at the beginning of the file: allow n severe errors where n is the number of input files. Without this statement, FUP will stop when it tries to create an input file that already exists. The number n should be equal to the number of input files that already exist. 4. Exit from the editor. 5.
PAGE 60
Running Data Build Generating Conversion Procedures (F3) 3. Exit Data Build. 4. Edit the file ZZTABLES and change the NonStop SQL default settings you want. 5. Enter OBEY XTABLES at the TACL prompt to create the NonStop SQL tables with the new settings. 6. Start Data Build. Do not rerun step F2 because it will wipe out the changes you made. Instead, complete steps F3 through F5 in Data Build. Data Build will create a conversion program that reflects the NonStop SQL tables you defined.
PAGE 61
Running Data Build Generating Conversion Procedures (F3) To create the conversion program: 1. Press F4 at the Main Menu. 2. Press any key to return to the Main Menu when the process finishes. 3. Press F12 to run Peruse and check spooler locations #CREATE and #PROGM. Correct any compiler errors or warnings before continuing. If you are creating a NonStop SQL conversion program, there are three spooler locations: #PROGM1, #PROGM2, and #PROGM3. 4. Note Exit Peruse to return to Data Build.
PAGE 62
Running Data Build Running the Conversion Program (F5) Running the In step F5, you will use the COBOL85 conversion program to convert the input files. Conversion Program You should have checked for errors and corrected them after each step in the (F5) conversion process. If you did not correct the errors, the conversion program will not properly convert the input files. To convert the input files: 1. Press F5 to run the conversion program.
PAGE 63
Running Data Build Handling Tape Input Handling Tape Input This subsection describes how to handle tape input in Data Build. Even though the basic steps are much the same as disk input, you do have to make some specific modifications for tape input. For example, you must verify the =TAPE DEFINE that specifies the tape location in the DEFINES and PATHCNFG files. This subsection briefly describes each step. You will find a complete discussion of each step in the appropriate subsection.
PAGE 64
Running Data Build Handling Tape Input 7. Press F14 to save the Conversion Environment settings. Then press SF16 to return to the Main Menu. 8. If the tape data is not in IXF format, go to step 9. If the tape data is in IXF format, the following applies to the Build (F2) step: Single-Tape Input The IN Data Files specification is used for the BUILD DDL (F2) step as follows: a. The input tape must be mounted before you press F2.
PAGE 65
Running Data Build Handling Tape Input c. After mounting the next tape, enter Y: BLDDDL: Attempt to Proceed? Enter Y/N:Y At the end of the last tape, you will get the following error message: BLDDDL: BLDDDL: BLDDDL: BLDDDL: IXF Tape Record/Block: xxxxx/xxxxx Tape Input File Error 021 No EOV1/EOV2 Encountered Assume End of Volume? Enter Y/N: Enter Y. Note For multiple volumes, in the BUILD (F2) step, all tapes must be mounted in the correct order as directed.
PAGE 66
5 Running the Sample Programs This section teaches you how to set up Tandem Data Build to create your own COBOL85 conversion programs. This procedures assumes you have already used the Project utility and created a project subvolume. Sample Data Data Build can create a conversion program that converts non-Tandem or Tandem Conversion input data files and writes the output to either Tandem Enscribe databases or to to Enscribe NonStop SQL tables.
PAGE 67
Running the Sample Programs Sample Data Conversion to Enscribe DEMO01, the COBOL85 group level record definition, is used as a logical record name in the Enscribe conversion program. The field names at the 02 level tell Data Build the field’s data format in the source system input file. The DEFINES File The next file you need to look at is the DEFINES file, which contains TACL DEFINEs. The DEFINEs in this file give Data Build information about the conversion process before the process starts.
PAGE 68
Running the Sample Programs Sample Data Conversion to Enscribe The three ADD DATAINnn DEFINEs are for the sample input files for both the Enscribe and SQL conversions. The three ADD DATAOTnn DEFINEs are for the sample Enscribe output file. When you do your own conversions, you do not have to recreate the DEFINES file. Instead, you can easily change the existing commands or add new ones if necessary. The PATHCNFG File The PATHCNFG file, a Pathway configuration file, defines Data Build’s Pathway parameters.
PAGE 69
Running the Sample Programs Sample Data Conversion to Enscribe RESET PROGRAM SET PROGRAM PRINTER $S.#PRINT <-- Change Spooler Location SET PROGRAM SECURITY "U" ADD PROGRAM LOGON-1, TYPE T16-6530 (INITIAL MAIN-MENU), TCP TCP-1 RESET SERVER SET SERVER DEFINE =DATAIN01, CLASS MAP, FILE $DATA01.KOD2016.DATAIN01 SET SERVER DEFINE =DATAIN02, CLASS MAP, FILE $DATA01.KOD2016.DATAIN02 SET SERVER DEFINE =DATAIN03, CLASS MAP, FILE $DATA01.KOD2016.DATAIN03 SET SERVER DEFINE =DATAOT01, CLASS MAP, FILE $DATA01.KOD2016.
PAGE 70
Running the Sample Programs Sample Data Conversion to Enscribe SET SERVER TMF ON, AUTORESTART 2 SET SERVER PARAM DATABUILD $DATA01.KOD2016 ADD SERVER T9154-HELP-SERV PATHCNFG names the location of each input and output file. For example, the following statement gives the location of the input file DATAIN01: SET SERVER DEFINE =DATAIN01, CLASS MAP, FILE $CONVERT.SUBVOL.DATAIN01 You will see a similar statement for each input and output file.
PAGE 71
Running the Sample Programs Sample Data Conversion to Enscribe Figure 5-1.
PAGE 72
Running the Sample Programs Sample Data Conversion to Enscribe To configure the Conversion Environment, follow these steps: 1. Pressing F1 at the Main Menu starts the process to display the Conversion Environment screen, shown in Figure 5-2. The first field, CONVERSION Subvolume, shows the name of the conversion volume and subvolume. The information and the location of the system software in Figure 5-2 will be slightly different on your terminal to match your project information and system environment.
PAGE 73
Running the Sample Programs Sample Data Conversion to Enscribe Field Descriptions The SOURCE Record Descriptions Files, DDL Record Descriptions Files, and DATA Files IN and OUT all begin with a one to six character prefix. For example, the name RECDEF identifies the file’s contents—source record descriptions. For DDL Record Descriptions Files, DDLDEF identifies the contents of the file—DDL record descriptions. Data Build assumes that the files are consecutively numbered starting with 01 to a maximum of 99.
PAGE 74
Running the Sample Programs Sample Data Conversion to Enscribe Building DDL Input Pressing F2 builds the data definitions and runs the DDL compiler. To build input for DDL, Data Build uses the Source Record Descriptions files, RECDEF01, RECDEF02, and RECDEF03, and builds a DDL input file for each from the record definitions that describes the data. The DDL statements and commands in the DDL input files are passed as input to the DDL compiler.
PAGE 75
Running the Sample Programs Sample Data Conversion to Enscribe 1 2 3 4 5 6 7 1 2 3 Definition 4 5 6 7 8 9 10 11 12 13 Section 14 15 16 17 18 19 20 21 22 COMP-3 23 24 25 26 5–10 ?DICT Audited dictionary created on subvol \D00.$DATA01.KOD2016. Dictionary opened on subvol \D00.$DATA01.KOD2016 for update access. ?COBOL COBDEFS ! Output source for COBOL is opened on \D00.$DATA01.KOD2016.COBDEFS ?TAL TALDEFS ! Output source for TAL is opened on \D00.$DATA01.KOD2016.
PAGE 76
Running the Sample Programs Sample Data Conversion to Enscribe 27 28 29 30 31 06 DEM1-CITY-X. 07 DEM1-CITY PIC X(18). 06 DEM1-CODE-X. 07 DEM1-CODE PIC X(14). END Record OUT-DEMO01 size is 98 bytes. Record OUT-DEMO01 added to dictionary. FUP output produced for OUT-DEMO01. TAL output produced for OUT-DEMO01. COBOL output produced for OUT-DEMO01. 8 ?SOURCE $DATA01.KOD2016.DDLDEF02 1 ?SECTION IN-DEMO02 2 RECORD IN-DEMO02. 3 FILE IS $DATA01.KOD2016.DATAIN02 ENTRY-SEQUENCED. 4 02 DEMO02.
PAGE 77
Running the Sample Programs Sample Data Conversion to Enscribe 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 06 DEM2-CODE1-X. 07 DEM2-CODE1 06 DEM2-CLASS-X. 07 DEM2-CLASS 06 DEM2-PRICE-X. 07 DEM2-PRICE 06 DEM2-COST1-X. 07 DEM2-COST1 06 DEM2-CARRIER-X. 07 DEM2-CARRIER 06 DEM2-COST2-X. 07 DEM2-COST2 06 DEM2-VENDOR-X. 07 DEM2-VENDOR 06 DEM2-DATE1-X. 07 DEM2-DATE1 06 DEM2-DATE2-X. 07 DEM2-DATE2 06 DEM2-DATE3-X. 07 DEM2-DATE3 06 DEM2-TIME-X. 07 DEM2-TIME END PIC X(1). PIC X(3).
PAGE 78
Running the Sample Programs Sample Data Conversion to Enscribe This step creates DDL files DDLDEF01, DDLDEF02, and DDLDEF03. These files are the Tandem DDL record definitions or description of the records in each input file. Data Build uses these definitions in the next step to create the procedures for the COBOL85 conversion program. This example of the DDL output contains an input and output section for each input file being converted. This information starts the proper conversion routine.
PAGE 79
Running the Sample Programs Sample Data Conversion to Enscribe When Data Build generates COBOL85 conversion procedures, it saves them in the library file PROCLIB. Those procedures are later copied, using COPY statements, into the COBOL85 conversion program. Following is an extract from the file PROCLIB. Note the procedures throughout this example converting the fields in each file. ?SECTION P-IN-DEMO01 * * * * * * * * IN-DEMO01 * * * * * * * * P-IN-DEMO01. MOVE LOW-VALUES TO WK-NULLINDS. MOVE ZERO TO SUB1.
PAGE 80
Running the Sample Programs Sample Data Conversion to Enscribe ADD ZERO TO ERR. COPY U-OUT-DEMO01 OF USERLIB. WRITE OUT-DEMO01. ADD 1 TO CTR-OUT. P-IN-DEMO01-EXIT. EXIT. ?SECTION P-IN-DEMO02 * * * * * * * * IN-DEMO02 * * * * * * * * P-IN-DEMO02. MOVE LOW-VALUES TO WK-NULLINDS. MOVE ZERO TO SUB1. COPY U-IN-DEMO02 OF USERLIB. P-IN-DEMO02-0001. COPY U-IN-DEMO02-DEM2-IDNUMBER OF USERLIB. MOVE DEM2-IDNUMBER OF IN-DEMO02 TO DEM2-IDNUMBER-X OF OUT-DEMO02. COPY U-OUT-DEMO02-DEM2-IDNUMBER OF USERLIB.
PAGE 81
Running the Sample Programs Sample Data Conversion to Enscribe P-IN-DEMO02-0009. COPY U-IN-DEMO02-DEM2-COST2 OF USERLIB. MOVE DEM2-COST2 OF IN-DEMO02 TO DEM2-COST2-X OF OUT-DEMO02. COPY U-OUT-DEMO02-DEM2-COST2 OF USERLIB. P-IN-DEMO02-0010. COPY U-IN-DEMO02-DEM2-VENDOR OF USERLIB. ENTER "CONVERT" USING DEM2-VENDOR OF IN-DEMO02. MOVE DEM2-VENDOR OF IN-DEMO02 TO DEM2-VENDOR OF OUT-DEMO02. COPY U-OUT-DEMO02-DEM2-VENDOR OF USERLIB. P-IN-DEMO02-0011. COPY U-IN-DEMO02-DEM2-DATE1 OF USERLIB.
PAGE 82
Running the Sample Programs Sample Data Conversion to Enscribe Creating the Enscribe Conversion Program Pressing F4 creates the data conversion program. Data Build uses the procedures from PROCLIB, created in the F3 step, to create and compile a COBOL85 data conversion program. 1. Press F4 at the Main Menu. Data Build displays the progress on your terminal tells you it is creating the conversion program as well as the starting and stopping time stamps.
PAGE 83
Running the Sample Programs Where to Go Next The conversion program converts the data in the three sample files: DATAIN01, DATAIN02, and DATAIN03. Data Build stores the data from each input file in a corresponding output file: DATAOT01, DATAOT02, and DATAOT03. As each file is converted, you will see the number of records converted. Remember, the file DEFINE names of the input and output files are specified in the Conversion Environment screen, so Data Build knows where to store the converted data.
PAGE 84
Running the Sample Programs Where to Go Next The samples have three Source Record Descriptions files: RECDEF01, RECDEF02, and RECDEF03, which is one Source Record Descriptions file for each input file. The following example is from RECDEF03, the source record descriptions for DEMO03: 01 DEMO03. <-- Output Table DEFINE Name 02 DEM3-DATE <-- Field Name PIC X(10) DATE. 02 DEM3-TIME PIC X(8) TIME. 02 DEM3-NUMBER PIC S9(4) COMP. 02 DEM3-DESCR PIC X(9). <-- Data Format 02 DEM3-TYPE PIC S9(4) COMP.
PAGE 85
Running the Sample Programs Where to Go Next The following DEFINEs name each table: DELETE DEFINE =DEMO01 ADD DEFINE =DEMO01, CLASS MAP, FILE $CONVERT.SQLCAT.DEMO01 DELETE DEFINE =DEMO02 ADD DEFINE =DEMO02, CLASS MAP, FILE $CONVERT.SQLCAT.DEMO02 DELETE DEFINE =DEMO03 ADD DEFINE =DEMO03, CLASS MAP, FILE $CONVERT.SQLCAT.DEMO03 When you convert data, you can easily change the existing DEFINEs or add new ones if necessary to the DEFINES file.
PAGE 86
Running the Sample Programs Where to Go Next An example of such a file is TBLSRC, which is provided with the samples. The following is the file TBLSRC: VOLUME $CONVERT.
PAGE 87
Running the Sample Programs Where to Go Next ,DEM3_NUMBER SMALLINT ,DEM3_DESCR CHAR (9) ,DEM3_TYPE SMALLINT ,DEM3_VENDORID INTEGER ,DEM3_WHLSLRID INTEGER ,DEM3_PARTNO INTEGER ,DEM3_PRICE NUMERIC (10,2) ,DEM3_CODE CHAR (2) ,DEM3_COST INTEGER ) ORGANIZATION ENTRY SEQUENCED CATALOG =CAT EXTENT (20,64) MAXEXTENTS 50 BUFFERED NO AUDIT ; NOT NULL NOT NULL NOT NULL DEFAULT SYSTEM NOT NULL NOT NULL NOT NULL DEFAULT SYSTEM NOT NULL NOT NULL DEFAULT SYSTEM NOT NULL CREATE INDEX DEM02I01 ON =DEMO02 (DEM2_SUBNUMBER
PAGE 88
Running the Sample Programs Where to Go Next When you run the Project utility, it builds the file TBLSRC to create the NonStop SQL catalog and one table for each input file for the sample conversion. The catalog contains the names and descriptions of each table and the names, data types, and sizes of the columns in each table. Notice that the CREATE TABLE command includes the table name, the name and data type of each column, and the name of the catalog with the table definition.
PAGE 89
Running the Sample Programs Where to Go Next Configuring the Conversion Environment Pressing F1 lets you configure the Conversion Environment. The Conversion Environment is where you define the settings for this conversion project, such as the names and locations of the input, output, DDL record definitions, and Source Record Descriptions files. To configure the Conversion Environment, follow these steps: 1.
PAGE 90
Running the Sample Programs Where to Go Next 2. Press any key to return to the Main Menu. At the bottom of the Main Menu, this message is displayed: BUILD Complete — CHECK DDL RESULTS ON SPOOLER. 3. To check the results on the spooler, press F12 to run Peruse. 4. Check the spooler locations #BLDDDL and #DDL. Make sure you check the last page of #DDL for any errors or warnings. If you find errors or warnings, correct them before going to the next step. 5. Exit Peruse and return to Data Build.
PAGE 91
Running the Sample Programs Where to Go Next Creating the SQL-LOAD Conversion Program Pressing F4 creates the data conversion program. Data Build uses the procedures from PROCLIB, created in the F3 step, to create and compile a COBOL85 data conversion program. To create the conversion program, do the following: 1. Press F4 at the Main Menu. Data Build displays the progress on your terminal showing the starting and stopping time stamps. The display information is simultaneously logged to the spooler. 2.
PAGE 92
Running the Sample Programs Summary The conversion program converts the data in sample files DATAIN01, DATAIN02, and DATAIN03. Data Build programmatically loads the data to the NonStop SQL tables you specified—DEMO01, DEMO02, and DEMO03. The DEFINES file names these tables. Data Build creates tables with these defaults: entry-sequenced, extents and maxextents, buffered, no audit, and no indexes. The NonStop SQL defaults are specified in the file TBLSRC.
PAGE 93
6 Customizing Data Build Field Conversion Input data fields are converted from: EBCDIC Character to ASCII Character Packed Decimal to Binary (Computational) Binary (Computational) and ASCII character fields are used as is and are not converted. The input field data format is specified in the Source Record Descriptions File. Based on this information, the BUILD DDL (F2) step creates the DDL Record Description File; each DDL input record field has a HEADING clause.
PAGE 94
Customizing Data Build Field Conversion A detailed example follows. Note that this example is for a conversion to Enscribe. For a conversion to NonStop SQL, the changes are functionally the same. These examples are based on the sample data provided with Data Build but with RECDEF03 not used and RECDEF02 changed to redefine RECDEF01. A listing of the Enscribe Conversion Program for the sample data can be found in Appendix B.
PAGE 95
Customizing Data Build Field Conversion b. Comment out all FD statements except for the main file. Move COPY statements for each additional format to below the main FD statement. FD F-IN-DEMO01 LABEL RECORDS ARE OMITTED. COPY IN-DEMO01 OF COBDEFS. COPY IN-DEMO01A OF COBDEFS. FD F-OUT-DEMO01 LABEL RECORDS ARE OMITTED. COPY OUT-DEMO01 OF COBDEFS. COPY OUT-DEMO01A OF COBDEFS. *FD F-IN-DEMO01A * LABEL RECORDS ARE OMITTED. *COPY IN-DEMO01A OR COBDEFS. *FD F-OUT-DEMO01A * LABEL RECORDS ARE OMITTED.
PAGE 96
Customizing Data Build Field Conversion d. Add record qualifying code to PROC-recordname and perform the appropriate P-IN-recordname procedure. PROC-DEMO01. MOVE SAVE-WK-STOR TO WK-STOR. MOVE "=DATAIN01" TO WK-DEFINE. . . OPEN INPUT F-IN-DEMO01 OUTPUT F-OUT-DEMO01. PROC-DEMO01-GO. READ F-IN-DEMO01 AT END GO TO PROC-DEMO01-END. ADD 1 TO CTR-IN. MOVE SPACES TO OUT-DEMO01.
PAGE 97
Customizing Data Build Changing Output to an Enscribe Key-Sequenced File Changing Output to an The Data Build default for Enscribe is a fixed-length entry sequenced output file. The Enscribe Key- following describes how to change the output to a key-sequenced file. Sequenced File 1. After the BUILD DDL (F2) step, exit Data Build and modify the output record definition in the DDL Record Description File. Before ... FILE IS ...... ENTRY-SEQUENCED. After ... FILE IS ...... KEY-SEQUENCED.
PAGE 98
Customizing Data Build Changing Output to an Enscribe Key-Sequenced File ACCESS MODE IS RANDOM RECORD KEY IS DEM1-PHONE-X OF OUT-DEMO01. 7. You can change the sequential WRITE statement in PROCLIB to include an INVALID KEY phrase. Before ... WRITE OUT-DEMO01. After ... WRITE OUT-DEMO01 INVALID KEY DISPLAY "INVALID KEY = " DEM1-PHONE-X OF OUT-DEMO01 GO TO END-JOB. 8. Exit Data Build, compile the modified program as follows and check the spooler for errors.
PAGE 99
Customizing Data Build Translating Unsupported Data Types Translating You can create your own customized routines to handle unsupported data type Unsupported Data translations. This example will use user exits. See the subsection “User Exits” below Types for a description of user exits including limitations. For this example, the approach is to do the following: 1. Add a holding field in working storage. 2. Perform a user supplied conversion for an input field. 3.
PAGE 100
Customizing Data Build User Exits 4. Create and compile the SIGNDNUM program. 5. If the compiled object module does not exist in the conversion subvolume, add a ? SEARCH statement at any user exit in USERLIB. 6. Complete the CREATE (F4) step and check the spooler output. 7. Perform the RUN (F5) step as usual.
PAGE 101
Customizing Data Build User Exits The user exit section names in USERLIB are: U-SPECIAL-NAMES just after the SPECIAL-NAMES paragraph statement U-WS just before the end of working storage U-PRE-PROCESS just before the first file is processed U-POST-PROCESS immediately after the last file is processed U-IN-filename for each file, immediately before processing each input record. filename is the record name on the 01 level from the corresponding Source Record Descriptions File.
PAGE 102
Customizing Data Build Discarding an Input Field The U-OUT-filename-fieldname exits are after the input field has been processed and the results moved to the output field. This exit can change the results of the standard code. The U-USER-ROUTINES exit is a good place to include any PERFORMed user-code routines, although such routines can be included in any user exit.
PAGE 103
Customizing Data Build Customizing SQL-LOAD Output 7. The PROCLIB file, created by the GENERATE (F3) Step, contains code to convert FIELDA (in place in the input record) and to move it to the output record. FIELDA was deleted from the output record in step 4 above. Modify PROCLIB to delete the following statement: MOVE FIELDA OF input-rec TO FIELDA OF output-rec. 8. Add code to the USERLIB file in user-exit section U-OUT-filename-FIELDA, the user exit after FIELDA is processed.
PAGE 104
Customizing Data Build Customizing SQL-LOAD Output SQLMSGS (supplied by Data Build to pass SQLCI messages to the conversion program) SQLCI The conversion program, ZZPROGMX is started under a separate TACL functionally like the following command: RUN ZZPROGMX /NAME $ZBLD, NOWAIT/ The conversion program has the following characteristics: Is a named process, $ZBLD. It must be started before SQLCI and SQLMSGS are started. Reads the input data (disk or tape).
PAGE 105
Customizing Data Build Customizing SQL-LOAD Output How Standard SQLCI Loads Nulls SQLCI indirectly supports loading null values as follows: The SQLCI LOAD command must include the USESQLNULLS and SOURCEREC options. The SOURCEREC option identifies the DDL record definition in the local DDL Dictionary (or a remote dictionary if the SOURCEDICT option is included). This record definition is for the input to SQLCI, which is the same as the output from the conversion program.
PAGE 106
Customizing Data Build Customizing SQL-LOAD Output 3. In the Source Record Descriptions File, for each nullable field with a NULLS option, there is an associated indicator field which is named the same but with a “-I” appended. This indicator field is specified by a NULLIND option and determines if the nullable field contains a null. If the associated indicator field contains LOW-VALUE (binary 0), the COBOL85 conversion program moves LOW-VALUE (binary 0) to the output field. 4.
PAGE 107
Customizing Data Build Customizing SQL-LOAD Output 2. After the BUILD (F2) step, change the NULL 0 in the DDL record definition to the null replacement value found above. 3. Run the following to update the DDL dictionary. DDL /IN ZZDDLIN, OUT $S.#DDLNEW/ Check spooler location #DDLNEW and correct any errors. 4. Customizing SQL-LOAD Nulls, Variation 1 Add user exit code for each nullable column to check if the input data for that column is null.
PAGE 108
Customizing Data Build Customizing SQL-LOAD Output DEM2-DATE2 in RECDEF02 Source Record Descriptions File is a nullable field because it contains a NULLS option. DEM2-DATE2 is a DATE column which does not naturally contain binary zero. If DEM2-DATE2 was an INTEGER column and if zero was a valid value represented in the data, the SQL-LOAD and SQL-INSERT would not produce identical results; all DEM2-DATE2 zero values would load as nulls for SQLLOAD.
PAGE 109
Customizing Data Build Customizing SQL-LOAD Output e. U-OUT-DEMO02-DEM2-DATE2 user exit tests the NULL-VALUE-IND switch and, if set, NULL-VALUE-REPLACE is moved to the DEM2-DATE2 field in the output record. ?SECTION U-WS 01 NULL-VALUE-COMPARE-SETUP. 02 FILLER PIC 9(8) COMP VALUE 0. 02 FILLER PIC 9(8) COMP VALUE 0. 02 FILLER PIC 9(8) COMP VALUE 0. 01 NULL-VALUE-COMPARE REDEFINES NULL-VALUE-COMPARE-SETUP PIC X(10). 01 NULL-VALUE-IND 88 NULL-FIELD PIC 9(4) COMP. VALUE 1. 01 NULL-VALUE-REPLACE-SETUP.
PAGE 110
Customizing Data Build Customizing SQL-LOAD Output 2. Run the following outside of Data Build to update the DDL dictionary: DDL /IN ZZDDLIN, OUT $S.#DDLNEW/ Check the spooler location #DDLNEW and correct any errors. 3. In Data Build, run the GENERATE (F3) step and then modify USERLIB to add user exit code, for example: ?SECTION U-WS 01 columname-LEN PIC S9(4). ?SECTION U-IN-filename-columname * This exit code is executed before the generated conversion code has * processed this field.
PAGE 111
Customizing Data Build Customizing SQL-LOAD Output But first, run without modification the sample data provided by Data Build. 1. RUN PROJECT to set up a conversion project subvolume. The DEFINES and PATHCNFG files are initially setup for the sample data and the NSSQL tables are created. 2. Set the default subvolume to the conversion project subvolume. 3. OBEY XSTART to start the Pathway System. 4. OBEY XRUN to start Data Build. 5. Press F1 (for the Configuration Environment screen).
PAGE 112
Customizing Data Build Customizing SQL-LOAD Output 6. Edit DDLDEF01 and change ?SECTION OUT-DEMO01 Before ... 06 DEM1-NAME-X. 07 DEM1-NAME PIC X(25). After ... 06 07 08 08 7. DEM1-NAME-X. DEM1-NAME. LEN VAL PIC S9(4) COMP. PIC X(25). Run the following: DDL /IN ZZDDLIN, OUT $S.#DDLNEW/ Check the spooler location #DDLNEW and correct any errors. 8. OBEY XRUN to start Data Build 9. Press F3 to generate PROCLIB. 10.
PAGE 113
Customizing Data Build Customizing SQL-LOAD Output ?SECTION U-WS 01 CTR PIC 9(4) COMP VALUE 0. ?SECTION U-POST-PROCESS * THE FOLLOWING PARAGRAPHS ARE PERFORMED BY USER EXIT CODE. * THEY CAN BE PLACED HERE, OR IN THE U-PRE-PROCESS EXIT. ALL OF THE * OTHER USER EXITS ARE PART OF PERFORMED PARAGRAPHS WHICH WILL BE * TERMINATED PREMATURELY IF PARAGRAPH NAMES ARE INCLUDED. * * MAKE SURE TO INCLUDE THE GO TO STATEMENT SO THAT THESE PARAGRAPHS * WILL NOT EXECUTE DURING THE U-POST-PROCESS EXIT.
PAGE 114
Customizing Data Build Customizing SQL-LOAD Output Example of Handling SQL-LOAD NULL VARCHAR DATA Now let’s take the previous information on handling VARCHAR data and loading nulls and put it all together. In order to process VARCHAR data, the DDL must contain the special GROUP containing the LEN and VAL fields. To load nulls for a VARCHAR column, the NULL clause must be added to the VAL field. The null replacement value must be replicated in every position of the full width VAL field.
PAGE 115
Customizing Data Build Customizing SQL-LOAD Output To run this example, perform the following steps: 1. Edit TBLSRC and change to make DEM1_NAME nullable VARCHAR: Before ... ,DEM1_NAME CHAR (25) NOT NULL, After ... ,DEM1_NAME 2. VARCHAR (25), OBEY XTBLSRC to change the SQL table definition. Press F1 (for the Configuration Environment Screen). For Option 1, specify SQLLOAD (not SQL-INSERT), and for Option 2 specify SORTED or leave it blank.
PAGE 116
Customizing Data Build Customizing SQL-LOAD Output 12. Modify USERLIB as follows: ?SECTION U-WS 01 CTR PIC 9(4) COMP VALUE 0. 01 NULL-VAL-REPLACE-SETUP. 02 FILLER PIC 9(4) COMP VALUE 514. 02 FILLER PIC 9(4) COMP VALUE 514. 02 FILLER PIC 9(4) COMP VALUE 514. 02 FILLER PIC 9(4) COMP VALUE 514. 02 FILLER PIC 9(4) COMP VALUE 514. 02 FILLER PIC 9(4) COMP VALUE 514. 02 FILLER PIC 9(4) COMP VALUE 514. 02 FILLER PIC 9(4) COMP VALUE 514. 02 FILLER PIC 9(4) COMP VALUE 514. 02 FILLER PIC 9(4) COMP VALUE 514.
PAGE 117
Customizing Data Build Customizing SQL-LOAD Output Sorting Data Before Using SQL-LOAD SQL-LOAD performance can be improved if the input is sorted in primary key sequence, in which case specify SORTED for Option 2 on the CONFIGURE (F1, Conversion Environment) screen. This directs Data Build to include the SORTED option when it prepares the SQLCI LOAD command in file ZZLOAD, thus bypassing a sort by SQLCI.
PAGE 118
Customizing Data Build Customizing SQL-LOAD Output D specifies descending order. E control fields to be modified in an E61 user exit before the data is sorted. DFSORT then collates the data in ascending order using the format (f) specified. Composite keys are acceptable; the AC format can be specified on a field basis. Note that computational data is the same on IBM and Tandem and should be ordered in standard IBM collating sequence.
PAGE 119
Customizing Data Build Customizing SQL-LOAD Output 3. Edit NEWLOAD and add CPU x (same x) to each of the SQLCI commands; there will be one SQLCI command for each table to be loaded by the conversion program, for example: SQLCI/CPU x, OUT $S.#ZZLOAD1/LOAD $ZBLD,=TABLE1,SORTED, & RECIN 0051, BLOCKIN 31977, SOURCEREC OUT-filename ; EXIT; 4. Insert the following command before the first SQLCI LOAD command. RUN ZZPROGMX /NAME $ZBLD, CPU y, NOWAIT/ 5.
PAGE 120
Customizing Data Build Creating Sample Input Data to Test Data Build Creating Sample Input When using Data Build to create a COBOL85 conversion program, it is very useful to Data to Test Data Build test with a small sample input file, especially if the input is on tape. Frequently a large file of real data is available. If this data is on tape, the following can be used to create a data tape with a few records for testing.
PAGE 121
Customizing Data Build Creating Sample Input Data to Test Data Build These commands copy the label information and data records to create a standard IBM labeled tape with a subset of the data records. The block count in the EOF1 label represents the total number of blocks from the full data tape, however, this field is not processed.
PAGE 122
7 Data Build Reference This section describes the Data Build menus. Main Menu Figure 7-1 shows the Data Build Main Menu: Figure 7-1.
PAGE 123
Data Build Reference Main Menu Main Menu Function Keys Table 7-1 show the function keys available from this menu and the action taken when you press the function key: Table 7-1.
PAGE 124
Data Build Reference Conversion Environment Menu Conversion Figure 7-2 shows the Data Build Conversion Environment menu: Environment Menu Figure 7-2. Conversion Environment for Samples C O N V E R S I O N E N V I R O N M E N T ============================================================================== CONVERSION Subvolume: $DATA.
PAGE 125
Data Build Reference Conversion Environment Menu Conversion Environment Selections Table 7-2 shows the selections available from the Conversion Environment menu. Following the table are detailed descriptions for the fields and options. Table 7-2 Conversion Environment Selections Selection Description Options (Defaults) CONVERSION Subvolume SOURCE Record Descriptions Files DDL Record Descriptions Files Data Files IN ($vol.
PAGE 126
Data Build Reference Conversion Environment Menu Conversion Subvolume Source Record Descriptions Files This field specifies the subvolume that will contain the Source Record Descriptions files, DDL record descriptions files, and data input files. This subvolume is also the subvolume where you ran the Project utility. This field specifies a prefix from 1 to 6 characters long of the file name of the Source Record Descriptions file.
PAGE 127
Data Build Reference Conversion Environment Menu Option 3 This field specifies the input format. Option Meaning Blank ASCII IXF Input is in IBM EBCDIC format (not in IXF nor ASCII file format.). Input is in ASCII format.. ASCII is required if you specify CSV in Option 4. Input is in Integration Exchange Format (IXF). IBM EBCDIC Input Data Build can convert IBM data in “raw” format. This format is neither IXF nor ASCII.
PAGE 128
Data Build Reference Conversion Environment Menu Note Option 4 QMF can format and extract data from DB2 databases in either QMF or IXF format; however, Data Build cannot convert data in QMF format. This field specifies whether your input is on tape or is in CSV file format. Option Meaning Blank =TAPE CSV=* Disk input, not in CSV format. Tape input CSV file format. You should replace the asterisk (*) with the delimiter used in your file (for example, if your delimiter is a comma, specify CSV=,).
PAGE 129
Data Build Reference Conversion Environment Menu Records can vary in length. Records in an unstructured file must be terminated with a CR/LF sequence.
PAGE 130
Data Build Reference Conversion Environment Menu A nullable field is defined in the Source Record Descriptions file (see Section 3 for a description of how to specify a nullable field in the Source Record Descriptions file). The number of columns in a row cannot exceed 500, the number of significant characters in a column cannot exceed 256, and the only supported data types are character, binary, date, time, and timestamp.
PAGE 131
Appendix A Data Build Files Because Data Build interacts with many other Tandem programs, including TACL, FUP, TAL, and COBOL85, many different files are used in a Data Build conversion. Certain files remain on the Data Build ISV and others are copied to the conversion project subvolume when you create a new project. This appendix tells you the name of each Data Build file, explains the significance of the file, and explains how it is used in the program.
PAGE 132
Data Build Files DICTfilename are the DDL data dictionary files created by running the DDL compiler in Data Build. The DDL dictionaries contain the objects defined in DDL source schema. FUPDEFS is a FUP file created by the DDL step (F2) in Data Build. PATHCNFG is the Pathway configuration file that defines Data Build’s Pathway parameters, terminal control processes (TCPs), programs, and server classes.
PAGE 133
Data Build Files XEDIT renumbers the source file for SQL-INSERT or SQL-LOAD. XINVOKE runs SQLCI commands to create the source record descriptions for the sample conversions. XRUN starts Data Build and displays the Main Menu. XSQLCMPL is an OBEY file that triggers the NonStop SQL and COBOL85 compiles when SQL-INSERT or SQL-LOAD are entered in the Conversion Environment. XSTART cold starts the Pathway system which runs Data Build. XSTOP stops the Pathway system.
PAGE 134
Sample Conversion Programs Appendix B This appendix contains COBOL85 Enscribe, SQL-LOAD, and SQL-INSERT conversion program listings generated by Data Build by following the directions in Section 5, “Running the Sample Programs.” Enscribe Conversion The following program is a sample Enscribe COBOL85 conversion program listing. Program Data Build creates this program for the samples when you use the Enscribe option in the Conversion Environment.
PAGE 135
Sample Conversion Programs Enscribe Conversion Program 42 RESERVE 2 AREAS 43 ASSIGN TO "=DATAOT03". 44 * 45 DATA DIVISION. 46 FILE SECTION. 47 * 48 FD 49 F-IN-DEMO01 LABEL RECORDS ARE OMITTED. 50 *COPY IN-DEMO01 OF COBDEFS. 3 < * Record IN-DEMO01 created on 08/09/93 at 15:25 4 < 5 < 6 < 03 DEM1-STATE PIC X(2). 7 < 03 DEM1-ZIPCODE PIC X(6). 8 < 03 DEM1-PHONE PIC X(6). 9 < 03 DEM1-NAME PIC X(25). 10 < 03 DEM1-ADDRESS PIC X(25). 11 < 03 DEM1-CITY PIC X(18).
PAGE 136
Sample Conversion Programs Enscribe Conversion Program 44 < 03 DEM2-VENDOR PIC X(5). 45 < 03 DEM2-DATE1 PIC X(10). 46 < 03 DEM2-DATE2-I PIC X. 47 < 03 DEM2-DATE2 PIC X(10). 48 < 03 DEM2-DATE3 PIC X(10). 49 < 03 DEM2-TIME PIC X(8). 59 * 60 FD 61 F-OUT-DEMO02 LABEL RECORDS ARE OMITTED. 62 *COPY OUT-DEMO02 OF COBDEFS. 51 < * Record OUT-DEMO02 created on 08/09/93 at 15:26 52 < 01 OUT-DEMO02. 53 < 02 DEMO02.
PAGE 137
Sample Conversion Programs Enscribe Conversion Program 94 < 03 DEM3-VENDORID PIC X(4). 95 < 03 DEM3-WHLSLRID PIC X(4). 96 < 03 DEM3-PARTNO PIC X(4). 97 < 03 DEM3-PRICE PIC X(6). 98 < 03 DEM3-CODE PIC X(2). 99 < 03 DEM3-COST PIC X(4). 67 * 68 FD 69 F-OUT-DEMO03 LABEL RECORDS ARE OMITTED. 70 *COPY OUT-DEMO03 OF COBDEFS. 101 < * Record OUT-DEMO03 created on 08/09/93 at 15:26 102 < 01 OUT-DEMO03. 103 < 02 DEMO03. 104 < 105 < 04 DEM3-DATE 106 < 03 DEM3-TIME-X.
PAGE 138
Sample Conversion Programs Enscribe Conversion Program 91 05 SWT-RECV-OPEN PIC X VALUE SPACE. 92 05 CONS-RESP PIC X VALUE SPACE. 93 05 WK-FNAME PIC X(8) VALUE SPACES. 94 01 SAVE-WK-STOR PIC X(100). 95 01 SYSTEM-VERSION PIC X(5) VALUE "N 20 ". 96 01 SYSTEM-SERIES PIC X VALUE "D". 97 01 WK-NULLINDS. PIC S9(4) COMP 98 05 WK-NULLIND 99 01 WK-DEFINE 100 01 WK-TIMESTAMP. 101 05 WK-DATE. 102 10 WK-YR PIC 9(4). 103 10 FILLER PIC X. 104 10 WK-MO PIC 99.
PAGE 139
Sample Conversion Programs Enscribe Conversion Program 147 05 W3-DA PIC 99. 148 01 W4-DATE PIC 9(8) 149 01 W4-DATEX REDEFINES W4-DATE 150 01 W5-WORK5. 151 05 W5-WORK5A PIC X. 152 05 W5-WORK5B PIC 9(5). 153 01 05 W5-WORK10A PIC X. 155 05 W5-WORK10B PIC 9(10). 01 W5-TEXT. 157 05 158 01 W5-TEXT1 PIC X OCCURS 12 TIMES. PIC X OCCURS 18 TIMES. W5-NUM18. 159 05 160 PIC X(4). W5-WORK10. 154 156 COMP. 01 W5-NUM18A W5-NUM18X REDEFINES W5-NUM18.
PAGE 140
Sample Conversion Programs Enscribe Conversion Program 200 DISPLAY "RUN: FILE NOT FOUND - =DATAOT01 - Proceeding..." 201 DISPLAY "RUN: FILE NOT FOUND - =DATAOT01 - Proceeding..." UPON LOG 202 GO TO PROC-DEMO01-EXIT. 203 DISPLAY "RUN:". 204 DISPLAY "RUN:" UPON LOG. 205 DISPLAY "RUN: Start -> DEMO01". 206 DISPLAY "RUN: Start -> DEMO01" UPON LOG. 207 OPEN INPUT F-IN-DEMO01 OUTPUT F-OUT-DEMO01. 208 PROC-DEMO01-GO. 209 READ F-IN-DEMO01 AT END GO TO PROC-DEMO01-END.
PAGE 141
Sample Conversion Programs Enscribe Conversion Program 256 DISPLAY "RUN: 257 IF CTR-OTHER NOT = ZERO DEMO02: OUT = " CTR-OUT UPON LOG. 258 DISPLAY "RUN: DEMO02: ??? = " CTR-OTHER 259 DISPLAY "RUN: DEMO02: ??? = " CTR-OTHER UPON LOG. 260 PROC-DEMO02-EXIT. 261 EXIT. * 262 PROC-DEMO03. 263 MOVE SAVE-WK-STOR TO WK-STOR. 264 MOVE "DEMO03" TO WK-FNAME. 265 MOVE "=DATAIN03" TO WK-DEFINE. 266 ENTER TAL "FIND^FILE" USING WK-DEFINE ERR.
PAGE 142
Sample Conversion Programs Enscribe Conversion Program 11 TO DEM1-STATE OF OUT-DEMO01. 12 12 * COPY U-OUT-DEMO01-DEM1-STATE OF USERLIB. < 13 P-IN-DEMO01-0002. 14 14 * COPY U-IN-DEMO01-DEM1-ZIPCODE OF USERLIB. < 15 ENTER "CONVERT" USING DEM1-ZIPCODE OF IN-DEMO01. 16 MOVE DEM1-ZIPCODE OF IN-DEMO01 17 TO DEM1-ZIPCODE OF OUT-DEMO01. 18 18 * COPY U-OUT-DEMO01-DEM1-ZIPCODE OF USERLIB. < 19 P-IN-DEMO01-0003. 20 20 * COPY U-IN-DEMO01-DEM1-PHONE OF USERLIB.
PAGE 143
Sample Conversion Programs Enscribe Conversion Program 53 ADD 1 TO CTR-OUT. 54 P-IN-DEMO01-EXIT. EXIT. 300 * 301 ?SOURCE PROCLIB (P-IN-DEMO02) 55 ?SECTION P-IN-DEMO02 56 * * * * * * * * 57 P-IN-DEMO02. 58 MOVE ZERO TO SUB1. 60 * COPY U-IN-DEMO02 OF USERLIB. < 61 P-IN-DEMO02-0001. 62 62 * COPY U-IN-DEMO02-DEM2-IDNUMBER OF USERLIB. < 63 MOVE DEM2-IDNUMBER OF IN-DEMO02 64 TO DEM2-IDNUMBER-X OF OUT-DEMO02. 65 65 * COPY U-OUT-DEMO02-DEM2-IDNUMBER OF USERLIB.
PAGE 144
Sample Conversion Programs Enscribe Conversion Program 94 P-IN-DEMO02-0007. 95 95 * COPY U-IN-DEMO02-DEM2-COST1 OF USERLIB. < 96 MOVE DEM2-COST1 OF IN-DEMO02 97 TO DEM2-COST1-X OF OUT-DEMO02. 98 98 * COPY U-OUT-DEMO02-DEM2-COST1 OF USERLIB. < 99 P-IN-DEMO02-0008. 100 100 * COPY U-IN-DEMO02-DEM2-CARRIER OF USERLIB. < 101 ENTER "CONVERT" USING DEM2-CARRIER OF IN-DEMO02. 102 MOVE DEM2-CARRIER OF IN-DEMO02 103 TO DEM2-CARRIER OF OUT-DEMO02.
PAGE 145
Sample Conversion Programs Enscribe Conversion Program 138 138 * COPY U-IN-DEMO02-DEM2-DATE3 OF USERLIB. < 139 ENTER "CONVERT" USING DEM2-DATE3 OF IN-DEMO02. 140 MOVE ZEROS TO WK-TIMESTAMP. 141 MOVE DEM2-DATE3 OF IN-DEMO02 TO WK-DATE. 142 PERFORM CONVERT-TIMESTAMP. 143 MOVE W2-JUL64 TO DEM2-DATE3 OF OUT-DEMO02. 144 144 * COPY U-OUT-DEMO02-DEM2-DATE3 OF USERLIB. < 145 P-IN-DEMO02-0014. 146 146 * COPY U-IN-DEMO02-DEM2-TIME OF USERLIB.
PAGE 146
Sample Conversion Programs Enscribe Conversion Program 182 182 * COPY U-IN-DEMO03-DEM3-NUMBER OF USERLIB. < 183 MOVE DEM3-NUMBER OF IN-DEMO03 184 TO DEM3-NUMBER-X OF OUT-DEMO03. 185 185 * COPY U-OUT-DEMO03-DEM3-NUMBER OF USERLIB. < 186 P-IN-DEMO03-0004. 187 187 * COPY U-IN-DEMO03-DEM3-DESCR OF USERLIB. < 188 ENTER "CONVERT" USING DEM3-DESCR OF IN-DEMO03. 189 MOVE DEM3-DESCR OF IN-DEMO03 190 TO DEM3-DESCR OF OUT-DEMO03. 191 191 * COPY U-OUT-DEMO03-DEM3-DESCR OF USERLIB.
PAGE 147
Sample Conversion Programs Enscribe Conversion Program 223 223 * COPY U-OUT-DEMO03-DEM3-CODE OF USERLIB. < 224 P-IN-DEMO03-0011. 225 225 * COPY U-IN-DEMO03-DEM3-COST OF USERLIB. < 226 MOVE DEM3-COST OF IN-DEMO03 227 TO DEM3-COST-X OF OUT-DEMO03. 228 228 * COPY U-OUT-DEMO03-DEM3-COST OF USERLIB. < 229 P-IN-DEMO03-9999. 230 ADD ZERO TO ERR. 231 231 * COPY U-OUT-DEMO03 OF USERLIB. < 232 WRITE OUT-DEMO03. 233 ADD 1 TO CTR-OUT. 234 304 P-IN-DEMO03-EXIT. EXIT.
PAGE 148
Sample Conversion Programs Enscribe Conversion Program 20 MOVE W1-DATE-TIME4 TO W2-DATE-TIME4. 21 MOVE W1-DATE-TIME5 TO W2-DATE-TIME5. 22 MOVE W1-DATE-TIME6 TO W2-DATE-TIME6. 23 MOVE W1-DATE-TIME7 TO W2-DATE-TIME7. 24 DISPLAY "RUN: 25 Start -> " W2-DATE-TIME2 "/" W2-DATE-TIME3 "/" W2-DATE-TIME1 " " W2-DATE-TIME4 ":" W2-DATE-TIME5 26 ":" 27 W2-DATE-TIME6.
PAGE 149
Sample Conversion Programs Enscribe Conversion Program 00 002 000006 010737 000044 M CONVERT-DATA 1993-08-09 15:27 COBOL85 \D00.$DATA01.KOD2016.ZZPROGM 00 003 010740 011201 010772 FIND^FILE 1993-07-21 13:26 TAL $DATA3.DATABLD.STALLIB 00 005 011674 012415 012207 PTOB 1993-07-21 13:26 TAL $DATA3.DATABLD.STALLIB DATA BLOCK MAP BY NAME FOR FILE: \D00.$DATA01.KOD2016.
PAGE 150
Sample Conversion Programs Enscribe Conversion Program The object file is executable on a Tandem NonStop System Number of compiler errors = 0 Number of compiler warnings = 0 Maximum symbol table size = 156232 bytes Elapsed time - 00:00:39 099331 Tandem Computers Incorporated B–17
PAGE 151
Sample Conversion Programs SQL-LOAD Conversion Program SQL-LOAD Conversion The following is a sample SQL-LOAD COBOL85 conversion program listing. Data Program Build creates this program to convert the sample input files when you use the SQLLOAD option in the Conversion Environment.
PAGE 152
Sample Conversion Programs SQL-LOAD Conversion Program 10 < 03 DEM1-ADDRESS PIC X(25). 11 < 03 DEM1-CITY PIC X(18). 12 < 03 DEM1-CODE PIC X(14). 780 * 800 FD F-IN-DEMO02 820 LABEL RECORDS ARE OMITTED. 840 *COPY IN-DEMO02 OF COBDEFS. 32 < * Record IN-DEMO02 version 1 updated on 08/09/93 at 15:49 33 < 01 IN-DEMO02. 34 < 02 DEMO02. 35 < 03 DEM2-IDNUMBER PIC X(2). 36 < 03 DEM2-DESCR PIC X(9). 37 < 03 DEM2-SUBNUMBER PIC X(2). 38 < 03 DEM2-CODE1 PIC X(1).
PAGE 153
Sample Conversion Programs SQL-LOAD Conversion Program 5 02 PARAMS-BUFFER-LEN 6 02 SRC-NAME-BUFFER-LEN PIC S9(4) COMP VALUE 34. 7 02 NUM-ERRORS PIC S9(4) COMP VALUE 0. 8 02 NEXT-P-OFFSET PIC S9(4) COMP VALUE 0. 9 02 FLAGS PIC S9(4) COMP VALUE 0. 10 02 PROCEDURE-ID PIC X(32) VALUE SPACES. 11 02 USER-LINE-NUMBER PIC S9(9) COMP VALUE -1. 12 02 SYNTAX-ERR-LOC PIC S9(4) COMP VALUE -1. 13 02 ERROR-LOCATION PIC X(40) VALUE SPACES. 14 02 ROWS PIC S9(9) COMP VALUE 0.
PAGE 154
Sample Conversion Programs SQL-LOAD Conversion Program 1200 * 1220 1240 EXEC SQL BEGIN DECLARE SECTION END-EXEC. ?SOURCE COBDEFS (OUT-DEMO01) 13 ?SECTION OUT-DEMO01,TANDEM 14 * Record OUT-DEMO01 version 1 updated on 08/09/93 at 15:49 15 01 OUT-DEMO01. 16 02 DEMO01. 17 03 DEM1-STATE-X. 18 04 DEM1-STATE 19 03 DEM1-ZIPCODE-X. 20 04 DEM1-ZIPCODE 21 04 DEM1-PHONE 23 03 DEM1-NAME-X. 24 04 DEM1-NAME 25 04 DEM1-ADDRESS 27 COMP. PIC X(25). PIC X(25). 03 DEM1-CITY-X.
PAGE 155
Sample Conversion Programs SQL-LOAD Conversion Program 81 03 DEM2-DATE3-X. 82 04 DEM2-DATE3 83 03 DEM2-TIME-X. 84 1340 1360 PIC S9(18) COMP. EXEC SQL END DECLARE SECTION END-EXEC. * 1380 1400 COMP. 04 DEM2-TIME PIC S9(18) EXEC SQL BEGIN DECLARE SECTION END-EXEC. ?SOURCE COBDEFS (OUT-DEMO03) 100 ?SECTION OUT-DEMO03,TANDEM 101 * Record OUT-DEMO03 version 1 updated on 08/09/93 at 15:49 102 01 OUT-DEMO03. 103 02 DEMO03. 104 03 DEM3-DATE-X. 105 04 DEM3-DATE 106 03 DEM3-TIME-X.
PAGE 156
Sample Conversion Programs SQL-LOAD Conversion Program 1840 01 WK-STOR. 1860 05 EOB PIC S9(4) COMP VALUE 0. 1880 05 EOF PIC S9(4) COMP VALUE 0. 1900 05 ERR PIC S9(4) COMP VALUE 0. 1920 05 N PIC S9(4) COMP VALUE 0. 1940 05 SUB1 PIC S9(4) COMP VALUE 0. 1960 05 SUB2 PIC S9(4) COMP VALUE 0. 1980 05 SUB3 PIC S9(4) COMP VALUE 0. 2000 05 SUB4 PIC S9(4) COMP VALUE 0. 2020 05 CTR-IN PIC S9(9) COMP VALUE 0. 2040 05 CTR-OUT PIC S9(9) COMP VALUE 0.
PAGE 157
Sample Conversion Programs SQL-LOAD Conversion Program 2960 05 W2-DATE-TIME4 PIC 99. 2980 05 W2-DATE-TIME5 PIC 99. 3000 05 W2-DATE-TIME6 PIC 99. 3020 05 W2-DATE-TIME7 PIC 99. 3040 01 W2-TIMESTAMP. 3060 05 W2-YR PIC 9(4) COMP. 3080 05 W2-MO PIC 9(4) COMP. 3100 05 W2-DA PIC 9(4) COMP. 3120 05 W2-HR PIC 9(4) COMP. 3140 05 W2-MN PIC 9(4) COMP. 3160 05 W2-SC PIC 9(4) COMP. 3180 05 W2-FRACTN1 PIC 9(4) COMP. 3200 05 W2-FRACTN2 PIC 9(4) COMP. COMP.
PAGE 158
Sample Conversion Programs SQL-LOAD Conversion Program 4060 EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC. 4080 * 4100 EXEC SQL CONTROL TABLE =DEMO01 SEQUENTIAL INSERT ON END-EXEC. 4120 EXEC SQL CONTROL TABLE =DEMO02 SEQUENTIAL INSERT ON END-EXEC. 4140 EXEC SQL CONTROL TABLE =DEMO03 SEQUENTIAL INSERT ON END-EXEC. 4160 * 4180 4180 * COPY U-PRE-PROCESS OF USERLIB. < 4200 PERFORM PROC-DEMO01 THRU PROC-DEMO01-EXIT. 4220 PERFORM PROC-DEMO02 THRU PROC-DEMO02-EXIT.
PAGE 159
Sample Conversion Programs SQL-LOAD Conversion Program 5140 MOVE "=DATAIN02" TO WK-DEFINE. 5160 ENTER TAL "FIND^FILE" USING WK-DEFINE ERR. 5180 IF ERR NOT = ZERO 5200 DISPLAY "RUN: FILE NOT FOUND - =DATAIN02 - Proceeding..." 5220 DISPLAY "RUN: FILE NOT FOUND - =DATAIN02 - Proceeding..." UPON LOG 5240 GO TO PROC-DEMO02-EXIT. 5260 DISPLAY "RUN:". 5280 DISPLAY "RUN:" UPON LOG. 5300 DISPLAY "RUN: Start -> DEMO02". 5320 DISPLAY "RUN: Start -> DEMO02" UPON LOG.
PAGE 160
Sample Conversion Programs SQL-LOAD Conversion Program 6260 DISPLAY "RUN: DEMO03: IN = " CTR-IN. 6280 DISPLAY "RUN: DEMO03: IN = " CTR-IN UPON LOG. 6300 DISPLAY "RUN: DEMO03: OUT = " CTR-OUT. 6320 DISPLAY "RUN: DEMO03: OUT = " CTR-OUT UPON LOG. 6340 IF CTR-OTHER NOT = ZERO 6360 DISPLAY "RUN: DEMO03: ??? = " CTR-OTHER 6380 DISPLAY "RUN: DEMO03: ??? = " CTR-OTHER UPON LOG. 6400 DISPLAY SB-MSGS-BUFF. 6420 DISPLAY SB-MSGS-BUFF UPON LOG. 6440 PROC-DEMO03-EXIT. EXIT.
PAGE 161
Sample Conversion Programs SQL-LOAD Conversion Program 35 TO DEM1-ADDRESS OF OUT-DEMO01. 36 36 * COPY U-OUT-DEMO01-DEM1-ADDRESS OF USERLIB. < 37 P-IN-DEMO01-0006. 38 38 * COPY U-IN-DEMO01-DEM1-CITY OF USERLIB. < 39 ENTER "CONVERT" USING DEM1-CITY OF IN-DEMO01. 40 MOVE DEM1-CITY OF IN-DEMO01 41 TO DEM1-CITY OF OUT-DEMO01. 42 42 * COPY U-OUT-DEMO01-DEM1-CITY OF USERLIB. < 43 P-IN-DEMO01-0007. 44 44 * COPY U-IN-DEMO01-DEM1-CODE OF USERLIB.
PAGE 162
Sample Conversion Programs SQL-LOAD Conversion Program 77 TO DEM2-SUBNUMBER-X OF OUT-DEMO02. 78 78 * COPY U-OUT-DEMO02-DEM2-SUBNUMBER OF USERLIB. < 79 P-IN-DEMO02-0004. 80 80 * COPY U-IN-DEMO02-DEM2-CODE1 OF USERLIB. < 81 ENTER "CONVERT" USING DEM2-CODE1 OF IN-DEMO02. 82 MOVE DEM2-CODE1 OF IN-DEMO02 83 TO DEM2-CODE1 OF OUT-DEMO02. 84 84 * COPY U-OUT-DEMO02-DEM2-CODE1 OF USERLIB. < 85 P-IN-DEMO02-0005. 86 86 * COPY U-IN-DEMO02-DEM2-CLASS OF USERLIB.
PAGE 163
Sample Conversion Programs SQL-LOAD Conversion Program 118 P-IN-DEMO02-0011. 119 119 * COPY U-IN-DEMO02-DEM2-DATE1 OF USERLIB. < 120 ENTER "CONVERT" USING DEM2-DATE1 OF IN-DEMO02. 121 MOVE ZEROS TO WK-TIMESTAMP. 122 MOVE DEM2-DATE1 OF IN-DEMO02 TO WK-DATE. 123 PERFORM CONVERT-TIMESTAMP. 124 MOVE W2-JUL64 TO DEM2-DATE1 OF OUT-DEMO02. 125 125 * COPY U-OUT-DEMO02-DEM2-DATE1 OF USERLIB. < 126 P-IN-DEMO02-0012. 127 127 * COPY U-IN-DEMO02-DEM2-DATE2 OF USERLIB.
PAGE 164
Sample Conversion Programs SQL-LOAD Conversion Program 163 ?SECTION P-IN-DEMO03 164 * * * * * * * * 165 P-IN-DEMO03. 166 MOVE ZERO TO SUB1. 168 * COPY U-IN-DEMO03 OF USERLIB. < 169 P-IN-DEMO03-0001. 170 170 * * * * * * * * MOVE LOW-VALUES TO WK-NULLINDS. 167 168 IN-DEMO03 * COPY U-IN-DEMO03-DEM3-DATE OF USERLIB. < 171 ENTER "CONVERT" USING DEM3-DATE OF IN-DEMO03. 172 MOVE ZEROS TO WK-TIMESTAMP. 173 MOVE DEM3-DATE OF IN-DEMO03 TO WK-DATE. 174 PERFORM CONVERT-TIMESTAMP.
PAGE 165
Sample Conversion Programs SQL-LOAD Conversion Program 206 P-IN-DEMO03-0007. 207 207 * COPY U-IN-DEMO03-DEM3-WHLSLRID OF USERLIB. < 208 MOVE DEM3-WHLSLRID OF IN-DEMO03 209 TO DEM3-WHLSLRID-X OF OUT-DEMO03. 210 210 * COPY U-OUT-DEMO03-DEM3-WHLSLRID OF USERLIB. < 211 P-IN-DEMO03-0008. 212 212 * COPY U-IN-DEMO03-DEM3-PARTNO OF USERLIB. < 213 MOVE DEM3-PARTNO OF IN-DEMO03 214 TO DEM3-PARTNO-X OF OUT-DEMO03. 215 215 * COPY U-OUT-DEMO03-DEM3-PARTNO OF USERLIB. < 216 P-IN-DEMO03-0009.
PAGE 166
Sample Conversion Programs SQL-LOAD Conversion Program 6780 LOAD-INIT. 6800 IF SWT-RECV-OPEN = SPACE 6820 ENTER TAL "OPEN^RECEIVE" 6840 USING SB-RECV-FNUM 6860 GIVING ERR. 6880 IF ERR = ZERO 6900 MOVE "1" TO SWT-RECV-OPEN 6920 ELSE 6940 DISPLAY "RUN: OPEN^RECEIVE ERROR = " ERR 6960 DISPLAY "RUN: OPEN^RECEIVE ERROR = " ERR UPON LOG 6980 GO TO END-JOB. 7000 MOVE 32000 TO SB-BLOCK-LEN. 7020 DIVIDE LOAD-RECLEN INTO SB-BLOCK-LEN. 7040 MULTIPLY LOAD-RECLEN BY SB-BLOCK-LEN.
PAGE 167
Sample Conversion Programs SQL-LOAD Conversion Program 7900 SQLUTIL-BUFF 7920 WK-BLOCK-LEN 7940 SB-REPLY-CODE 7960 SB-TRACE-FLAG 7980 SB-OUT-FNUM 8000 GIVING ERR. 8020 IF ERR NOT = ZERO 8040 DISPLAY "RUN: REPLY^TO^SQLUTIL ERROR = " ERR 8060 DISPLAY "RUN: REPLY^TO^SQLUTIL ERROR = " ERR UPON LOG 8080 GO TO END-JOB. 8100 MOVE ZERO TO SB-INDEX SB-BUFF-LEN SB-REPLY-CODE. 8120 8140 LOAD-TALK-TO-SQLUTIL-EXIT. EXIT. * 8160 LOAD-CLOSEDOWN.
PAGE 168
Sample Conversion Programs SQL-LOAD Conversion Program 4 DISPLAY " 5 DISPLAY " T9515D20 - 01JUN93 - " SYSTEM-SERIES "-series Operating System". 6 DISPLAY " T9515D20 - 01JUN93 - " SYSTEM-SERIES "-series Operating System" 7 Tandem Data Build - DATA CONVERSION FACILITY " UPON LOG. UPON LOG. 8 DISPLAY "COPYRIGHT TANDEM COMPUTERS INCORPORATED 1992, 1993". 9 DISPLAY "COPYRIGHT TANDEM COMPUTERS INCORPORATED 1992, 1993" UPON LOG. 10 DISPLAY "==================================================".
PAGE 169
Sample Conversion Programs SQL-LOAD Conversion Program 49 ":" 50 W2-DATE-TIME6. DISPLAY "RUN: 51 Stop -> " W2-DATE-TIME2 "/" W2-DATE-TIME3 "/" W2-DATE-TIME1 " " W2-DATE-TIME4 ":" W2-DATE-TIME5 52 ":" 53 W2-DATE-TIME6 UPON LOG. TIME-STOP-EXIT. 9180 EXIT. * 9200 *COPY U-USER-ROUTINES OF USERLIB.
PAGE 170
Sample Conversion Programs SQL-LOAD Conversion Program BASE LIMIT TYPE MODE NAME 000024 000057 SPECIAL WORD DATE TIME LANGUAGE #ERUG 1993-08-09 16:08 COBOL85 #G0 1993-08-09 16:08 COBOL85 #HIGHBUF 1993-08-09 16:08 COBOL85 #RUCB 1993-08-09 16:08 COBOL85 CONVERT-DATA 1993-08-09 16:08 COBOL85 CONVERT-DATA# 1993-08-09 16:08 COBOL85 EXTENDED#STACK#POINTERS 1993-07-21 13:26 TAL SOURCE FILE \D00.$DATA01.KOD2016.ZZPROGM 000000 000017 SPECIAL WORD \D00.$DATA01.KOD2016.
PAGE 171
Sample Conversion Programs SQL-LOAD Conversion Program 9 20 4114 35 Code pages Primary data words Secondary data words Data pages 0 Resident code pages 80 Extended data pages 4134 Top of stack location in words 1 Code segment 0 Binder Warnings 0 Binder Errors COBOL85 - T9257D20 - (06 JUL 93) The object file is executable on a Tandem NonStop System Number of compiler errors = 0 Number of compiler warnings = 0 Maximum symbol table size = 201232 bytes Elapsed time - 00:00:50 B–38 099331 Tandem
PAGE 172
Sample Conversion Programs SQL-INSERT Conversion Program SQL-INSERT The following is a sample SQL-INSERT COBOL85 conversion program listing. Data Conversion Program Build creates this program to convert the sample input files when you use the SQLINSERT option in the Conversion Environment.
PAGE 173
Sample Conversion Programs SQL-INSERT Conversion Program 6 < 03 DEM1-STATE PIC X(2). 7 < 03 DEM1-ZIPCODE PIC X(6). 8 < 03 DEM1-PHONE PIC X(6). 9 < 03 DEM1-NAME PIC X(25). 10 < 03 DEM1-ADDRESS PIC X(25). 11 < 03 DEM1-CITY PIC X(18). 12 < 03 DEM1-CODE PIC X(14). 840 * 860 FD F-IN-DEMO02 880 LABEL RECORDS ARE OMITTED. 900 *COPY IN-DEMO02 OF COBDEFS. 32 < * Record IN-DEMO02 version 2 updated on 08/09/93 at 19:11 33 < 34 < 35 < 03 DEM2-IDNUMBER PIC X(2).
PAGE 174
Sample Conversion Programs SQL-INSERT Conversion Program 1180 ?NOLIST 1240 1260 EXEC SQL END DECLARE SECTION END-EXEC. * 1280 EXEC SQL INCLUDE SQLCA END-EXEC. 1 01 SQLCA. 2 02 EYE-CATCHER PIC X(2) 3 02 VERSION PIC S9(4) COMP VALUE 0. 4 02 NUM-ERR-ENTRIES PIC S9(4) COMP VALUE 7. 5 02 PARAMS-BUFFER-LEN PIC S9(4) COMP VALUE 180. 6 02 SRC-NAME-BUFFER-LEN PIC S9(4) COMP VALUE 34. 7 02 NUM-ERRORS PIC S9(4) COMP VALUE 0. 8 02 NEXT-P-OFFSET PIC S9(4) COMP VALUE 0.
PAGE 175
Sample Conversion Programs SQL-INSERT Conversion Program 22 03 SQL-STATEMENT-TYPE PIC 9(4) COMP. 23 88 SQL-STATEMENT-SELECT VALUE 1. 24 88 SQL-STATEMENT-INSERT VALUE 2. 25 88 SQL-STATEMENT-UPDATE VALUE 3. 26 88 SQL-STATEMENT-DELETE VALUE 4. 27 88 SQL-STATEMENT-DDL VALUE 5. 28 88 SQL-STATEMENT-CONTROL VALUE 6. 29 1340 88 SQL-STATEMENT-DCL 1360 1380 VALUE 7. * EXEC SQL BEGIN DECLARE SECTION END-EXEC.
PAGE 176
Sample Conversion Programs SQL-INSERT Conversion Program 73 04 DEM2-COST2 74 03 DEM2-VENDOR-X. 75 04 DEM2-VENDOR 76 03 FILLER 77 03 DEM2-DATE1-X. 04 DEM2-DATE1 79 03 DEM2-DATE2-X. 80 04 DEM2-DATE2 81 03 DEM2-DATE3-X. 82 04 DEM2-DATE3 83 03 DEM2-TIME-X. 84 PIC S9(18) COMP. PIC S9(18) COMP. PIC S9(18) COMP. PIC S9(18) COMP. EXEC SQL END DECLARE SECTION END-EXEC. * 1520 1540 PIC X(5). 04 DEM2-TIME 1480 COMP. PIC X(1).
PAGE 177
Sample Conversion Programs SQL-INSERT Conversion Program 1820 05 N PIC S9(4) COMP VALUE 0. 1840 05 SUB1 PIC S9(4) COMP VALUE 0. 1860 05 SUB2 PIC S9(4) COMP VALUE 0. 1880 05 SUB3 PIC S9(4) COMP VALUE 0. 1900 05 SUB4 PIC S9(4) COMP VALUE 0. 1920 05 CTR-IN PIC S9(9) COMP VALUE 0. 1940 05 CTR-OUT PIC S9(9) COMP VALUE 0. 1960 05 CTR-OTHER PIC S9(9) COMP VALUE 0. 1980 05 CTR-ERROR PIC S9(9) COMP VALUE 0. 2000 05 CTR-CSV.
PAGE 178
Sample Conversion Programs SQL-INSERT Conversion Program 2940 01 W2-TIMESTAMP. 2960 05 W2-YR PIC 9(4) COMP. 2980 05 W2-MO PIC 9(4) COMP. 3000 05 W2-DA PIC 9(4) COMP. 3020 05 W2-HR PIC 9(4) COMP. 3040 05 W2-MN PIC 9(4) COMP. 3060 05 W2-SC PIC 9(4) COMP. 3080 05 W2-FRACTN1 PIC 9(4) COMP. 3100 05 W2-FRACTN2 PIC 9(4) COMP. COMP. 3120 01 W2-JUL64 PIC S9(18) 3140 01 W3-DATE PIC 9(8). 3160 01 W3-DATEX REDEFINES W3-DATE. 3180 05 W3-YR PIC 9(4).
PAGE 179
Sample Conversion Programs SQL-INSERT Conversion Program 4020 PERFORM PROC-DEMO01 THRU PROC-DEMO01-EXIT. 4040 PERFORM PROC-DEMO02 THRU PROC-DEMO02-EXIT. 4060 PERFORM PROC-DEMO03 THRU PROC-DEMO03-EXIT. 4080 4080 4100 * COPY U-POST-PROCESS OF USERLIB. < * 4120 END-JOB. 4140 PERFORM TIME-STOP THRU TIME-STOP-EXIT. 4160 4180 STOP RUN. * 4200 PROC-DEMO01. 4220 MOVE SAVE-WK-STOR TO WK-STOR. 4240 MOVE "DEMO01" TO WK-FNAME. 4260 MOVE "=DATAIN01" TO WK-DEFINE.
PAGE 180
Sample Conversion Programs SQL-INSERT Conversion Program 5120 GO TO PROC-DEMO02-EXIT. 5140 DISPLAY "RUN:". 5160 DISPLAY "RUN:" UPON LOG. 5180 DISPLAY "RUN: Start -> DEMO02". 5200 DISPLAY "RUN: Start -> DEMO02" UPON LOG. 5220 OPEN INPUT F-IN-DEMO02. 5240 OPEN OUTPUT F-SQLMSGS. 5260 ENTER TAL "COBOLFILEINFO" 5280 USING F-SQLMSGS 5300 OMITTED OMITTED SB-MSGS-FNUM. 5320 PROC-DEMO02-GO. 5340 READ F-IN-DEMO02 AT END GO TO PROC-DEMO02-END. 5360 ADD 1 TO CTR-IN.
PAGE 181
Sample Conversion Programs SQL-INSERT Conversion Program 6240 CLOSE F-SQLMSGS. 6260 DISPLAY "RUN: DEMO03: IN = " CTR-IN. 6280 DISPLAY "RUN: DEMO03: IN = " CTR-IN UPON LOG. 6300 DISPLAY "RUN: DEMO03: OUT = " CTR-OUT. 6320 DISPLAY "RUN: DEMO03: OUT = " CTR-OUT UPON LOG. 6340 IF CTR-OTHER NOT = ZERO 6360 DISPLAY "RUN: DEMO03: ??? = " CTR-OTHER 6380 DISPLAY "RUN: DEMO03: ??? = " CTR-OTHER UPON LOG. 6400 DISPLAY SB-MSGS-BUFF. 6420 DISPLAY SB-MSGS-BUFF UPON LOG.
PAGE 182
Sample Conversion Programs SQL-INSERT Conversion Program 34 MOVE DEM1-ADDRESS OF IN-DEMO01 35 TO DEM1-ADDRESS OF OUT-DEMO01. 36 36 * COPY U-OUT-DEMO01-DEM1-ADDRESS OF USERLIB. < 37 P-IN-DEMO01-0006. 38 38 * COPY U-IN-DEMO01-DEM1-CITY OF USERLIB. < 39 ENTER "CONVERT" USING DEM1-CITY OF IN-DEMO01. 40 MOVE DEM1-CITY OF IN-DEMO01 41 TO DEM1-CITY OF OUT-DEMO01. 42 42 * COPY U-OUT-DEMO01-DEM1-CITY OF USERLIB. < 43 P-IN-DEMO01-0007. 44 44 * COPY U-IN-DEMO01-DEM1-CODE OF USERLIB.
PAGE 183
Sample Conversion Programs SQL-INSERT Conversion Program 78 ENTER "CONVERT" USING DEM2-DESCR OF IN-DEMO02. 79 MOVE DEM2-DESCR OF IN-DEMO02 80 TO DEM2-DESCR OF OUT-DEMO02. 81 81 * COPY U-OUT-DEMO02-DEM2-DESCR OF USERLIB. < 82 P-IN-DEMO02-0003. 83 83 * COPY U-IN-DEMO02-DEM2-SUBNUMBER OF USERLIB. < 84 MOVE DEM2-SUBNUMBER OF IN-DEMO02 85 TO DEM2-SUBNUMBER-X OF OUT-DEMO02. 86 86 * COPY U-OUT-DEMO02-DEM2-SUBNUMBER OF USERLIB. < 87 P-IN-DEMO02-0004.
PAGE 184
Sample Conversion Programs SQL-INSERT Conversion Program 119 < 120 P-IN-DEMO02-0010. 121 121 * COPY U-IN-DEMO02-DEM2-VENDOR OF USERLIB. < 122 ENTER "CONVERT" USING DEM2-VENDOR OF IN-DEMO02. 123 MOVE DEM2-VENDOR OF IN-DEMO02 124 TO DEM2-VENDOR OF OUT-DEMO02. 125 125 * COPY U-OUT-DEMO02-DEM2-VENDOR OF USERLIB. < 126 P-IN-DEMO02-0011. 127 127 * COPY U-IN-DEMO02-DEM2-DATE1 OF USERLIB. < 128 ENTER "CONVERT" USING DEM2-DATE1 OF IN-DEMO02. 129 MOVE ZEROS TO WK-TIMESTAMP.
PAGE 185
Sample Conversion Programs SQL-INSERT Conversion Program 165 165 * COPY U-OUT-DEMO02 OF USERLIB. < 166 MOVE WK-NULLINDS TO SQL-NULLINDS.
PAGE 186
Sample Conversion Programs SQL-INSERT Conversion Program 212 212 * COPY U-OUT-DEMO03-DEM3-NUMBER OF USERLIB. < 213 P-IN-DEMO03-0004. 214 214 * COPY U-IN-DEMO03-DEM3-DESCR OF USERLIB. < 215 ENTER "CONVERT" USING DEM3-DESCR OF IN-DEMO03. 216 MOVE DEM3-DESCR OF IN-DEMO03 217 TO DEM3-DESCR OF OUT-DEMO03. 218 218 * COPY U-OUT-DEMO03-DEM3-DESCR OF USERLIB. < 219 P-IN-DEMO03-0005. 220 220 * COPY U-IN-DEMO03-DEM3-TYPE OF USERLIB.
PAGE 187
Sample Conversion Programs SQL-INSERT Conversion Program 252 < 253 MOVE DEM3-COST OF IN-DEMO03 254 TO DEM3-COST-X OF OUT-DEMO03. 255 255 * COPY U-OUT-DEMO03-DEM3-COST OF USERLIB. < 256 P-IN-DEMO03-9999. 257 ADD ZERO TO ERR. 258 258 * COPY U-OUT-DEMO03 OF USERLIB. < 259 MOVE WK-NULLINDS TO SQL-NULLINDS.
PAGE 188
Sample Conversion Programs SQL-INSERT Conversion Program 7180 USING W2-TIMESTAMP 7200 GIVING W2-JUL64. 7220 * 7240 ?SOURCE $DATA02.ZDATABLD.COBSOURC (PROG-BANNER) 1 ?SECTION PROG-BANNER 2 PROG-BANNER. 3 DISPLAY " Tandem Data Build - DATA CONVERSION FACILITY ". 4 DISPLAY " Tandem Data Build - DATA CONVERSION FACILITY " UPON LOG. 5 DISPLAY " T9515D20 - 01JUN93 - " SYSTEM-SERIES "-series Operating System".
PAGE 189
Sample Conversion Programs SQL-INSERT Conversion Program 40 MOVE W1-DATE-TIME5 TO W2-DATE-TIME5. 41 MOVE W1-DATE-TIME6 TO W2-DATE-TIME6. 42 MOVE W1-DATE-TIME7 TO W2-DATE-TIME7. 43 DISPLAY "RUN:". 44 DISPLAY "RUN:" UPON LOG. 45 DISPLAY "RUN: RUN DATA CONVERSION PROGRAM COMPLETE". 46 DISPLAY "RUN: RUN DATA CONVERSION PROGRAM COMPLETE" UPON LOG. 47 DISPLAY "RUN: Stop 48 -> " W2-DATE-TIME2 "/" W2-DATE-TIME3 "/" W2-DATE-TIME1 " " W2-DATE-TIME4 ":" W2-DATE-TIME5 49 ":" 50 W2-DATE-TIME6.
PAGE 190
Sample Conversion Programs SQL-INSERT Conversion Program 00 002 000010 011462 000053 M CONVERT-DATA 1993-08-09 19:14 COBOL85 \D00.$DATA01.KOD2016.ZZPROGM 00 003 011463 011724 011515 FIND^FILE 1993-07-21 13:26 TAL $DATA3.DATABLD.STALLIB 00 006 013111 013632 013424 PTOB 1993-07-21 13:26 TAL $DATA3.DATABLD.STALLIB 00 007 013633 014225 013641 READ^SQLMSGS2 1993-07-21 13:26 TAL $DATA3.DATABLD.STALLIB DATA BLOCK MAP BY NAME FOR FILE: \D00.$DATA01.KOD2016.
PAGE 191
Sample Conversion Programs SQL-INSERT Conversion Program 0001000250 0001000375 COMMON STRING __SQLVAR__00751521542564646942 1993-08-09 19:14 COBOL85 0001000442 0001000713 COMMON \D00.$DATA01.KOD2016.ZZPROGM STRING __SQLVAR__00751521542565331373 1993-08-09 19:14 COBOL85 0001000760 0001001165 COMMON \D00.$DATA01.KOD2016.ZZPROGM STRING __SQLVAR__00751521542566032751 1993-08-09 19:14 COBOL85 \D00.$DATA01.KOD2016.ZZPROGM RUN TIME DATA UNIT MAP BY NAME FOR FILE: \D00.$DATA01.KOD2016.
PAGE 192
Appendix C Conversion Worksheets This appendix has two worksheets and a checklist. You can reproduce these and fill them in as you work on a conversion project.
PAGE 193
Conversion Worksheets Project Utility Worksheet Project Utility The Project Utility Worksheet (Table C-1) lets you define the information about a new Worksheet conversion project, including the conversion subvolume name and the location of the system and utility files, before creating a conversion project subvolume. Section 3, “Setting Up the Project Environment” describes this worksheet in detail. Table C-1. Project Utility Worksheet Item Default Information Example PROJECT Subvolume ($volume.
PAGE 194
Conversion Worksheets Conversion Environment Worksheet Conversion The Conversion Environment Worksheet (Table C-2) lets you complete the Environment information for the Conversion Environment screen (step F1 in Data Build). See Worksheet Section 4, “Running Data Build,” for more information about this worksheet. Table C-2.
PAGE 195
Conversion Worksheets Set Up Project Environment Set Up Project The Set Up Project Environment checklist (Table C-3) provides a detailed list of steps Environment Checklist to prepare the environment before you run Data Build. See Section 3 for more information. Table C-3. Set Up Project Environment Checklist Done? Conversion Step 1. Complete the Project Utility Worksheet 2. Volume to the Data Build Install subvolume 3. 4.
PAGE 196
Conversion Worksheets Run Data Build Run Data Build Checklist The Run Data Build checklist (Table C-4) provides a detailed list of steps to run Data Build. See Section 4 for more information. Table C-4. Run Data Build Checklist Done? Conversion Step Notes 1. Complete the Conversion Environment worksheet 2. Volume to the conversion project subvolume 3. Obey XSTART (to start Pathway) 4. Obey XRUN (to start Data Build) 5. Press F12 to run Peruse; check the spooler and correct any errors 6.
PAGE 197
Appendix D Tandem Reserved Words for IXF Input Tandem products have reserved words that are not reserved in the source system. For IXF input data only, Data Build provides the ZZRESRVD file as a mechanism for overcoming this condition by adding a one-character suffix to Tandem reserved words occurring in the input data, which gives them a new name that is not reserved.
PAGE 198
Appendix E Troubleshooting This appendix presents a series of steps to be used in diagnosing problems encountered in Data Build. 1. Check all spooler output after each step. Correct any errors and rerun the step. An undetected error in one step can show up as a problem in a subsequent step. It is strongly recommended that ALL spooler output be checked following each step. Press F12 from the main menu to enter PERUSE. If an error occurs, it should be corrected and processing repeated at the failing step.
PAGE 199
Troubleshooting Conversion Environment c. The DEFINES file on the project subvolume. d. The PATHLOG (Pathway log) file on the project subvolume, by using the command: FUP COPY $volume.projsubvolume.PATHLOG,$S.#PATHLOG,H,SHARE e. The CONFIG file on the project subvolume, by using the command: FUP COPY $volume.projsubvolume.CONFIG,$S.#CONFIG,H,SHARE f. Each record descriptions file on the project subvolume. g. Each DDL file on the project subvolume. h.
PAGE 200
Troubleshooting Spooler Output Spooler Output All terminal output, program messages and displays are logged to the spooler at each step. 1. The PROJECT step creates four spooler files called #PROJECT and one called #TBLSRC. 2. The PATHCOM configuration startup is written to #PLOG when Data Build is cold started. 3. Each subsequent step produces one or more spooler files.
PAGE 201
Troubleshooting Files Produced or Modified Files Produced or Data Build creates new files or modifies existing files at each step to be used in Modified subsequent conversion steps. RECDEFnn, DDLDEFnn and DATAOTnn names are based on the prefix assigned on the CONFIGURE (F1) Screen.
PAGE 202
Glossary COBOL85. The Tandem compiler and run-time support for the American National Standard Programming Language COBOL, X3.23-1985. Data Definition Language (DDL). A product for defining data objects in Enscribe files and translating object definitions into source code for programming languages and other products on the NonStop system. database. A collection of logically related records or files, such as employees of a business or parts in an inventory.
PAGE 203
Index A ALLOWnnnnn option 7-9 ASCII data 6-1 option 7-6 B Blank option 7-5 C Checklist Run Data Build C-5 Set Up Project Environment C-4 COBOL85 conversion program 6-11 system requirements 1-5 Comma Separated Values (CSV) 1-4 Commands CLEAR 4-19 CREATE TABLE 5-23 QMF 2-10 SQLCI INVOKE 3-12, 5-22 TAPECOM 4-19 Conversion Environment configuring 4-7, 5-24 field descriptions 4-9, 5-7 menu 7-3 samples 5-6 screen 4-7, 5-6 selections and options 7-4 worksheet C-3 Conversion program COBOL85 1-1 creating 4-14 custom
PAGE 204
Index Conversion steps steps outside of Data Build 3-3 steps within Data Build 4-3 Conversion subvolume configuring 5-6 field 7-5 field settings 4-9, 5-8 CPU designation, changing 3-15 CREATE TABLE command 5-23 CSV converting to 7-7 file format 7-7 input 1-4, 7-7 option 7-7 D Data checking after conversion 4-16, 5-18, 5-27 input files 5-8 output files 4-12, 5-8 Data Build exit 5-6 run 4-6, 5-5, 5-23 Data Extract (DXT) product 2-10, 7-6 Data fields ASCII 6-1 binary 6-1 converting 6-1 Data files IN field 7-5
PAGE 205
Index Databases, types created 1-4 DATETIME type column loading 6-1 DDL field 7-5 input 4-11, 5-9, 5-24 output 5-9 record descriptions files 4-9, 5-8 Defaults Data Build 4-7 Enscribe 6-5 NonStop SQL 4-13 DEFINES file Enscribe 3-13 naming conventions 2-9 NonStop SQL 3-14 samples 5-2, 5-19 E Editors EDIT 3-11, 4-11 TEDIT 3-11, 4-11 Enscribe defaults 6-5 DEFINES file 3-13 option 7-5 output files 4-12, 5-8 PATHCNFG file 3-13 sample conversion 2-3, 5-1 sample conversion program 5-17, B-1 system requirements 1-5
PAGE 206
Index Files DDL record descriptions 5-8 DEFINES 2-1, 3-14, 5-2 descriptions of A-1 FUPDEFS 6-5 library 6-8 naming conventions 2-2, 2-4, 2-6, 2-8 NonStop SQL 5-20 PATHCNFG 2-1, 3-15, 5-3 PROCLIB 5-14, 5-25 sample input 5-1 source record descriptions 2-1, 3-12, 5-1, 5-8 TBLSRC 5-20 used for conversions 2-1 USERLIB 6-8 XRUN 2-1 XSTART 2-1 XSTOP 2-1 ZZDDLIN 4-12 ZZRESRVD D-1 ZZTABLES 4-13 Filesets 2-1 Function keys, main menu 7-2 FUP checking data with 4-16, 5-18 output files, creating 4-12 I Input files DDL 5
PAGE 207
Index IXF format 2-10 option 7-6 IXF data characteristics 7-6 supported 1-4 M Main Menu 1-2, 4-6, 5-5, 7-1 Menus Conversion Environment 7-3 Main 7-1 N NonStop SQL defaults, changing 4-13 DEFINES file 3-14 PATHCNFG file 3-14 sample conversion 5-18 sample conversion program 5-26 system requirements 1-5 tables, creating 5-20, 5-22 Nulls customizing 6-14 loading 6-13 SQL-LOAD option 6-13 standard SQLCI 6-13 O Operating system 1-5 OPTIONS fields description 5-8, 7-5 settings 4-9 Output files changing 6-5 creati
PAGE 208
Index P PATHCNFG file changing after installation 3-15 Enscribe 3-13 naming conventions 2-9 NonStop SQL 3-14 samples 2-8, 5-3, 5-20 Pathway cold start 4-6 parameters, changing 3-15 Peruse See spooler Problem determination D-1, E-1 Processes SQLCAT 6-12 SQLCI 6-12 SQLMSGS 6-12 SQLUTIL 6-12 PROCLIB 4-14, 5-14, 5-25 Project utility running 3-5 worksheet C-2 Q Query Management Facility (QMF) 2-10, 7-6 R Record formats combining 6-1 fixed length 6-1 multiple 6-1 Reserved words file D-1 Run Data Build checklist
PAGE 209
Index S Sample conversion Enscribe 5-1 NonStop SQL 5-18 Screen Conversion Environment 4-7, 5-6 Main Menu 1-2, 7-1 Set Up Project Environment checklist C-4 SORTED option 5-24, 7-5 Sorting data 6-25 Source record descriptions COBOL record definitions 3-11 creating 7-6 DDL 4-11 field 7-5 field settings 4-9 files 5-8 naming files 3-12 NonStop SQL tables 3-11 preparing 3-11 samples 5-1, 5-18 table 3-10 Spooler changing 3-15 checking 4-4 SQL-INSERT sample conversion program B-39 option 7-5 SQL-LOAD customizing 6
PAGE 210
Index SQLCI-LOAD process efficiencies 6-1 SQLMSGS process 6-12 SQLUTIL process 6-12 System requirements 1-5 System software location 4-11, 5-8 T TAPE option 7-7 Tape input 2-10, 4-16 TAPECOM commands 4-19 restrictions 4-19 Troubleshooting D-1, E-1 U User exits code 6-9 section names 6-9 supported 6-8 V VSAM Access Method Services 7-6 W Worksheet Conversion Environment C-3 Project utility C-2 Z ZZDDLIN file 4-12 ZZRESRVD file D-1 ZZTABLES file 4-13 Index–8 099331 Tandem Computers Incorporated