DataLoader/MX Reference Manual Abstract This manual describes the features and functionality of the DataLoader/MX product, a tool to load HP NonStop™ SQL/MX, SQL/MP, and Enscribe databases. Product Version DataLoader/MX G10 Supported Release Version Updates (RVUs) This publication supports G06.23 and all subsequent G-series RVUs until otherwise indicated by its replacement publication.
Document History Part Number Product Version Published 525872-001 DataLoader/MX G10 April 2004 525872-002 DataLoader/MX G10 September 2004
DataLoader/MX Reference Manual Index Figures Tables What’s New in This Manual vii Manual Information vii New and Changed Information About This Manual ix Audience ix Organization of This Manual Related Documentation x Notation Conventions xiv vii ix 1. Introduction to DataLoader/MX DataLoader/MX Features 1-1 DataLoader/MX Applications 1-1 Loading Scenario 1-2 Relationship to Other Loading Tools 1-3 Using DataLoader/MX With SQL/MP and Enscribe 1-3 2.
. Specifying File-Related Options for DataLoader/MX Contents Building Your Loading Application 3-13 Analyzing Your Configuration 3-14 4. Specifying File-Related Options for DataLoader/MX Interpretations 4-1 BROADCAST 4-2 CSV 4-2 DUMP 4-6 EBCDIC 4-6 INDIRECT 4-7 KEYRANGE 4-8 TEXT 4-9 Modifiers 4-9 BUFSIZE 4-9 MAX 4-9 NOREWIND 4-10 NOUNLOAD 4-10 num% 4-10 NUMBUFS 4-11 PERSIST 4-11 RECFORM 4-11 SUBTYPE 4-12 TYPE 4-12 WAIT 4-13 5.
6.
A. Error and Warning Messages Contents Batch Totals 7-7 Multiprocess Considerations 7-8 Parallel Considerations 7-8 A. Error and Warning Messages Conversion Routine Messages A-1 User Exit Messages A-1 File System Messages A-2 Partition Boundary Estimation Messages $RECEIVE Messages A-10 Test Data Generation Messages A-10 TMF Messages A-11 Miscellaneous Messages A-13 Warnings A-13 A-9 B. Processing Flowcharts C. C-Only Error Functions Index Figures Figure 1-1. Figure 2-1. Figure 6-1. Figure 6-2.
Tables Contents Tables Table 2-1. Table 5-1. Table 5-2. Table 5-3.
Contents DataLoader/MX Reference Manual—525872-002 vi
What’s New in This Manual Manual Information DataLoader/MX Reference Manual Abstract This manual describes the features and functionality of the DataLoader/MX product, a tool to load HP NonStop™ SQL/MX, SQL/MP, and Enscribe databases. Product Version DataLoader/MX G10 Supported Release Version Updates (RVUs) This publication supports G06.23 and all subsequent G-series RVUs until otherwise indicated by its replacement publication.
New and Changed Information What’s New in This Manual DataLoader/MX Reference Manual—525872-002 viii
About This Manual This manual describes DataLoader/MX software, which is used to load and maintain NonStop SQL/MX, SQL/MP, and Enscribe databases. It contains reference information for the product and describes its features and syntax. Audience This manual was written for database administrators and programmers. End users can also use this manual.
Related Documentation About This Manual Section Title This section... 6 DataLoader/MX Examples Provides examples to demonstrate Dataloader/MX. These examples represent the four basic loading scenarios from which nearly all real loading scenarios are derived, as well as their use with FTP.
Related Documentation About This Manual SQL/MX Messages Manual Describes SQL/MX messages. SQL/MX Glossary Defines SQL/MX terminology. Programming Manuals SQL/MX Programming Manual for C and COBOL Describes how to embed SQL/MX statements in ANSI C and COBOL programs. SQL/MX Programming Manual for Java Describes how to embed SQL/MX statements in Java programs according to the SQLJ standard.
Related Documentation About This Manual The following manuals are part of the SQL/MP library of manuals and are essential references for information about SQL/MP Data Definition Language (DDL) and SQL/MP installation and management: Related SQL/MP Manuals SQL/MP Reference Manual Describes the SQL/MP language elements, expressions, predicates, functions, and statements. SQL/MP Installation and Management Guide Describes how to plan, install, create, and manage an SQL/MP database.
Related Documentation About This Manual This figure shows the manuals in the SQL/MX library: Programming Manuals Introductory Guides SQL/MX Comparison Guide for SQL/MP Users SQL/MX Programming Manual for C and COBOL SQL/MX Quick Start SQL/MX Programming Manual for Java SQL/MX Guide to Stored Procedures in Java Reference Manuals SQL/MX Reference Manual SQL/MX Messages Manual SQL/MX Glossary SQL/MX Queuing and Publish/ Subscribe Services SQL/MX Query Guide SQL/MX Report Writer Guide DataLoader
Notation Conventions About This Manual Notation Conventions General Syntax Notation The following list summarizes the notation conventions for syntax presentation in this manual. UPPERCASE LETTERS. Uppercase letters indicate keywords and reserved words; enter these items exactly as shown. Items not enclosed in brackets are required. For example: MAXATTACH lowercase italic letters. Lowercase italic letters indicate variable items that you supply. Items not enclosed in brackets are required.
Notation for Messages About This Manual … Ellipsis. An ellipsis immediately following a pair of brackets or braces indicates that you can repeat the enclosed sequence of syntax items any number of times. For example: M address-1 [ , new-value ]... [ - ] {0|1|2|3|4|5|6|7|8|9}... An ellipsis immediately following a single syntax item indicates that you can repeat that syntax item any number of times. For example: "s-char..." Punctuation.
Notation for Messages About This Manual lowercase italic letters. Lowercase italic letters indicate variable items whose values are displayed or returned. For example: p-register process-name [ ] Brackets. Brackets enclose items that are sometimes, but not always, displayed. For example: Event number = number [ Subject = first-subject-value ] A group of items enclosed in brackets is a list of all possible items that can be displayed, of which one or none might actually be displayed.
1 Introduction to DataLoader/MX Dataloader/MX is a software product that is used to load and maintain NonStop SQL/MP, SQL/MX, and Enscribe databases. DataLoader/MX Features DataLoader/MX takes full advantage of the parallelism of the NonStop SQL/MX database product. DataLoader/MX is designed to handle massive amounts of data with an emphasis on efficient performance.
Loading Scenario Introduction to DataLoader/MX number of source files for load customization. DataLoader/MX works in conjunction with the import utility. Loading Scenario A typical load scenario might involve a load operation from a single input source into three partitions of a table. You need a DataLoader/MX process for the input stream and a DataLoader/MX process for each load operation into a specific partition. Figure 1-1 illustrates this scenario. Figure 1-1.
Introduction to DataLoader/MX Relationship to Other Loading Tools Relationship to Other Loading Tools DataLoader/MX is designed to complement other tools that load and maintain NonStop SQL/MX databases. For example, the NonStop SQL/MX import utility is not, in itself, parallel, but can be used in conjunction with DataLoader/MX to provide a parallel load solution. In addition, DataLoader/MX can be customized to perform data format transformations as required when using import for load operations.
Introduction to DataLoader/MX Using DataLoader/MX With SQL/MP and Enscribe DataLoader/MX Reference Manual—525872-002 1 -4
2 DataLoader/MX Components This section describes the file package, structure, and file system of DataLoader/MX. DataLoader/MX File Package DataLoader/MX is delivered with the object file, dataload, as well as with text files with prototypes for its utility procedures. You can copy and modify these procedures to control processing with your own version of DataLoader/MX.
DataLoader/MX Components DataLoader/MX Structure DataLoader/MX Structure Internally, DataLoader/MX consists of three major components—the DataLoader/MX main logic, user exits, and the DataLoader/MX library. Figure 2-1 shows the structure of DataLoader/MX. Figure 2-1. Structure of DataLoader/MX DataLoader/MX Main Logic User Exits Library VST016.vsd Main Logic The main logic component performs the basic program control.
DataLoader/MX Components DataLoader/MX File System DataLoader/MX File System To provide important loading features that are not directly supplied by the Enscribe file system, DataLoader/MX layers its own file system on top of the Guardian file system. The DataLoader/MX file system is designed to support DataLoader/MX, so some of the capabilities in the underlying Guardian file system are not used. For example, DataLoader/MX processes files sequentially, so it does not use random I/O.
DataLoader/MX Components ° • DataLoader/MX File System The file must be opened in exclusive mode. (Input files can be opened in nonexclusive mode.) Guardian files. DataLoader/MX can read and write Guardian files. DataLoader/MX determines that a file is a Guardian file if the file name begins with one of these: ° ° ° ° ° $ \ = /G /E If an OSS file name is being opened for output and the file does not exist, DataLoader/MX creates the file and writes to it as an unstructured file.
DataLoader/MX Components DataLoader/MX File System Table 2-1. DataLoader/MX I/O Blocking Behavior Max. Block Size BLKSIZE Modifier Allowed? Max. Rec Length RECFORM= FB Allowed? RECFORM= VB Allowed? RECFORM= IBMVB Allowed? EDIT file N.A. 1 No 239 No No No EDIT file N.A. 1 No 4072 if input No No No Process 32000 No 32000 Yes 3 Yes 5 No $RECEIVE 32000 No 32000 Yes 3 Yes 5 Yes 6 Structured File N.A.
DataLoader/MX Components DataLoader/MX File System DataLoader/MX process from another process to writes to $RECEIVE. You can use $RECEIVE as input and output at the same time. There are many benefits to using $RECEIVE as a normal input or output file when you use other utilities. For example, import can get its input from a named DataLoader/MX process that has its output stream set as $RECEIVE.
3 Running DataLoader/MX This section explains how to run DataLoader/MX. It describes parameters defined by HP and gives examples of the use of these parameters. It also discusses parallelism and how to build your load application to run DataLoader/MX most effectively. Syntax of DataLoader/MX To start DataLoader/MX, run the DataLoader/MX program and give it a list of parameters. These parameters, together with defined user exits, determine what DataLoader/MX does.
Running DataLoader/MX Syntax of DataLoader/MX INDIRECT TEXT MAX=size NOREWIND NOUNLOAD num% NUMBUFS=numbufs RECFORM= { FB<{RECSIZE=recsize [,PADCHAR=padchar ]> } { VB[ ] } { IBMVB } PERSIST SUBTYPE=subtype-num TYPE=type-num WAIT=secs o-mod is: BROADCAST BUFSIZE=bufsize CSV [ <[SEPCHAR=sepchar ] [,CONTCHAR=contchar ] [ ,MAXRECLEN=len ]> ] DUMP [ <{HEX | ASCII}> ] INDIRECT KEYRANGE TEXT MAX=size NOREWIND NOUNLOAD num% NUMBUFS=numbufs RECFORM= { FB<{RECSIZE=recsize [,PADCHAR=padchar]> } { VB
Running DataLoader/MX Syntax of DataLoader/MX must follow the parameter name immediately, separated by an equal sign (=). If additional information is associated with the value, it must follow the value and be enclosed in angle brackets (<>). The parameter and its value cannot have embedded spaces. The parameter and its value are not case-sensitive, except for any OSS file names specified. If interpretations or modifiers are present, then the value has to be enclosed within double quotes.
Running DataLoader/MX • • • • Syntax of DataLoader/MX If -G is specified and -I= is not specified, DataLoader/MX does not open an input file. When it needs a record to process, it calls the GETNEXTRECORD exit. If -I= is specified and -G is not specified, DataLoader/MX opens the input file and fetches the data to be processed. If -I= and -G are both specified, DataLoader/MX opens the input file but does not read data from it directly.
Running DataLoader/MX Syntax of DataLoader/MX bufsize is the size of the buffers. sepchar is an alternate separation character. The default value is a comma (,). contchar is an alternate continuation character. The default value is an ampersand (&). len is the maximum record length. Its default value is 239. num% is a percent number of records. Decimal point is allowed. numbufs is the number of buffers to use. recsize is the record size. padchar is the character to be used for padding.
Running DataLoader/MX Syntax of DataLoader/MX -L=num-errs directs DataLoader/MX to terminate if the number of error messages reaches numerrs errors. If this parameter is not specified, DataLoader/MX continues regardless of the number of errors detected. -O=out-file[(o-mod[,o-mod]...)] specifies an output file to write, with optional modifiers or interpretations, or both.
Running DataLoader/MX Syntax of DataLoader/MX len is the maximum record length. Its default value is 239. num% is a percent number of records to write. Decimal point is allowed. numbufs is the number of buffers to use. recsize is the record size. padchar is the character to be used for padding. blksize is the block size to use. secs is the number of seconds to wait.
Running DataLoader/MX Syntax of DataLoader/MX did not redirect stdout, you used the -Q option, you can obtain more information by rerunning the job without -Q. -S=num-recs [] directs DataLoader/MX to print periodic status messages. num-recs is the number of input records that are processed between status messages. if specified, causes messages to appear in a comma-separated value (CSV) format suitable for loading into a spreadsheet for analysis or graphical display.
Running DataLoader/MX Considerations—DataLoader/MX Process records (as recorded in the restart file) are skipped. The restart file is an Enscribe file and therefore has a Guardian name rather than an OSS name. PURGE directs DataLoader/MX to purge the file containing restart information on normal termination. After abnormal termination, the file still exists. KEEP directs DataLoader/MX to never purge the file. You must purge it after determining that the load application was successful.
Running DataLoader/MX Considerations—DataLoader/MX Process Specifying File Names Except when specifying the TMF restart file with the -T parameter, use this format to specify a file for DataLoader/MX: filename [(feature [,feature]...])] specifies the OSS file name or Guardian file name, including the node name, volume, and subvolume or DEFINE name.
Running DataLoader/MX Considerations—DataLoader/MX Process where mydef has been previously added as a DEFINE. Using the -P Parameter The -P parameter is rarely used in a production data load but is very useful when you initially set up a database. Suppose that you have a large amount of data, perhaps containing hundreds of tapes. You know it contains 500,000,000 records. You want to partition this data over ten disks but have only a rough idea of the key values.
Running DataLoader/MX Default Exit Examples Default Exit Examples The default DataLoader/MX user exits perform neutral actions when called. These syntax examples show how to run DataLoader/MX with its default exits: Command Result $ dataload -q Directs DataLoader/MX to not write informational messages to the home terminal. $ dataload -e=ERROR Directs DataLoader/MX to write error messages to a file named ERROR. $ dataload -E=ERROR Directs DataLoader/MX to write error messages to a file named ERROR.
Running DataLoader/MX • Creating Parallelism Taking advantage of parallelism Creating Parallelism If the input is a single stream, such as a set of tapes that cannot be processed separately or a single LAN transfer that cannot be changed into multiple simultaneous transfers, you must break the single input stream into multiple streams that can be processed in parallel.
Running DataLoader/MX Analyzing Your Configuration more import processes. Configuring and tuning your loading application are discussed next. You can create customized versions of DataLoader/MX with combinations of user routines appropriate to what you want to do. As you customize each version with the appropriate user exits, you should give these versions names that indicate their purpose or position in the application hierarchy.
Running DataLoader/MX Analyzing Your Configuration For most configurations, the statistics provided through the -S= parameter should be sufficient. However, you can also use Measure and other performance tools. When you run DataLoader/MX with the -S= parameter, it generates statistics about the internal buffer pool. DataLoader/MX uses this pool to buffer data for the downstream reader processes (typically other DataLoader/MX processes).
Running DataLoader/MX Analyzing Your Configuration DataLoader/MX Reference Manual—525872-002 3- 16
4 Specifying File-Related Options for DataLoader/MX DataLoader/MX has two types of file-related options: interpretations and modifiers. An interpretation specifies how the contents of a file are handled. A modifier stipulates a more specific attribute of a load, such as the maximum number of records to be transferred. Interpretations and modifiers are passed in the file name to the open procedure (DTLOPEN).
Specifying File-Related Options for DataLoader/MX BROADCAST For example, suppose that the contents of a file, file1, are: ABCDED F12345 A734 By default, a program opening file1 with the TEXT interpretation would normally read the records as ABCDED, F12345, and A734. But there could be other interpretations. For example, the records might be hexadecimal representations of the data to be read.
Specifying File-Related Options for DataLoader/MX CSV CSV[] can be any combination of the following, separated by commas and enclosed in angle brackets: SEPCHAR=charspec Changes the separator character from a comma to another character. charspec can either be the new separator character itself or its representation in decimal. If specified, the new separator character applies only to the data and not to the translation specifications.
Specifying File-Related Options for DataLoader/MX CSV field-type is CHAR, INT, or DATETIME. length is the length of the field. length is required for CHAR and INT field types but is not allowed for DATETIME, which is always eight bytes long. For INT type fields, length must be one, two, four, or eight bytes.
Specifying File-Related Options for DataLoader/MX • • • • • • CSV Data records can be continued onto multiple lines by putting the ampersand (&} character (or the CONTCHAR) at the end of each line of the record except the last. The data record continuation is not related to any continuation used in the record layout description. Blank lines can appear between the lines of a continued record and are ignored. If you use the CONTCHAR, it replaces the ampersand (&). See record 2.
Specifying File-Related Options for DataLoader/MX DUMP DUMP Specify the DUMP interpretation to direct the file system to handle the contents of a file as hexadecimal and ASCII dumps of the actual records. It has this format: DUMP[
Specifying File-Related Options for DataLoader/MX INDIRECT probably not desirable. In such cases, you can supply a CONVERTIT or MISCn user exit in which you can use the library function DTLEBCDICToASCII() to convert only the fields that are character fields. INDIRECT Specify the INDIRECT interpretation to direct the file system to handle the contents of a file as a list of file names. These file names can themselves have DataLoader/MX features.
Specifying File-Related Options for DataLoader/MX KEYRANGE DataLoader/MX does not usually split a record across two files. If the current file cannot hold the entire next record, the whole record is placed in the next file. The same result occurs when multiple output files are created because of errors 43 or 45. Splitting does not occur if the output files are either Guardian files or unstructured files on OSS.
Specifying File-Related Options for DataLoader/MX TEXT TEXT The TEXT interpretation directs DataLoader/MX to read data from the specified file by handling the new-line character as a record delimiter. The resulting file contains as many records as there are lines in the file. The records are of variable length, the record length being the length of the line, exclusive of the new-line character. Only OSS files (code 180) can be given this interpretation.
Specifying File-Related Options for DataLoader/MX NOREWIND Use the MAX modifier in conjunction with the INDIRECT modifier when dividing a single large input file into a number of smaller files of a given size. NOREWIND Specify the NOREWIND modifier only for tape files if you want the tape to be left extended when DataLoader/MX closes it. For an input file, the tape remains at the point where the last read left it.
Specifying File-Related Options for DataLoader/MX NUMBUFS NUMBUFS Use NUMBUFS to specify the number of I/O buffers to be allocated for a file. It has the format: NUMBUFS=num num can range from two to ten. Use NUMBUFS for process files. PERSIST If $RECEIVE is your input or output file, you can use the PERSIST option. With this option specified, DataLoader/MX does not terminate when the number of openers falls to 0 (zero) but waits for additional opens.
Specifying File-Related Options for DataLoader/MX SUBTYPE FB specifies that the block contains fixed, blocked records. Each block contains an integral number of records. The format of fbspecs is: RECSIZE=num [,PADCHAR=charspec] where num is the length of the record in bytes, and charspec indicates what character to use to pad short records on output. VB specifies that the block contains records that are variable in length.
Specifying File-Related Options for DataLoader/MX WAIT open of a DataLoader/MX process, to maintain performance, the records that flow between them should be blocked. For this scenario to occur, the DataLoader/MX process must appear to the COBOL library as a tape drive (device type 4).
Specifying File-Related Options for DataLoader/MX DataLoader/MX Reference Manual—525872-002 4- 14 WAIT
5 Creating a Customized Version of DataLoader/MX Although DataLoader/MX default functions might be enough for any loading scenario, you might want to create a customized version of DataLoader/MX. You can do this by combining user exits and utility routines. Utility routines are provided for such tasks as data conversion, communication with the user, error checking, and generating test data.
Creating a Customized Version of DataLoader/MX User Exits Table 5-1.
Creating a Customized Version of DataLoader/MX User Exit Descriptions Table 5-2. Parameter -X Exit Codes (page 2 of 2) C CONVERTIT Continue with next exit. Dump record to -E file. Read next record. Read next record. D DELETEIT Go to post-processing. Continue with next exit. Read next record. I INSERTIT Go to post-processing. Continue with next exit. Read next record. U UPDATEIT Go to post-processing. Continue with next exit. Read next record.
Creating a Customized Version of DataLoader/MX BUILDKEY The C prototypes are described next. The sexitscb.cob file, described on page 2-1, contains COBOL models. BUILDKEY The BUILDKEY exit is given the input record and builds the key associated with the record. The syntax is: void BUILDKEY( short* Purpose, char* Record, long* RecordLen, char* KeyBuf, long* KeyBufLen, long* KeyLen, short* Status) BUILDKEY operates as if the record’s key is the first 255 bytes, or the length of the record if it is shorter.
Creating a Customized Version of DataLoader/MX CONVERTIT The syntax is: void CHECKARG( char* Arg, long* ArgLen, short* Status ) This exit provides a way to pass parameters to other user exits. When you process a parameter that you recognize, save whatever information from it that you need for your global variables so that it is accessible to other user exits. If the parameter is one that is recognized by your user exits, it should be processed and a nonzero value returned in Status.
Creating a Customized Version of DataLoader/MX DELETEIT DELETEIT The DELETEIT exit is called when you explicitly request it by specifying the letter d or D in the list of user exits called through the -X parameter. Its function is to attempt to delete a row from the database. The syntax is: void DELETEIT( char*Rec, long* RecLen, short* Status ) If the deletion is successful, DELETEIT should return a positive value. If the deletion is not successful, it should return a negative value.
Creating a Customized Version of DataLoader/MX EXITSDESCRIPTION EXITSDESCRIPTION The EXITSDESCRIPTION exit is called to obtain a string that is descriptive of the user exits. This string is printed in DataLoader/MX’s startup banner.
Creating a Customized Version of DataLoader/MX INITIALIZE2 The syntax is: void INITIALIZE1( short* Status ) If initialization is successful, move a nonzero value to Status. If initialization fails, move a 0 (zero) value to Status. If you return a (0) zero value, DataLoader/MX terminates abnormally. If you want to display messages about the cause of the initialization failure, you can call DTLWriteErr to display a message and then return a (0) zero value to Status. Note.
Creating a Customized Version of DataLoader/MX • • • INSTRUCTIONS Checks SQLSTATE Updates DataLoader/MX internal statistics Returns a value appropriate for the Status return from INSERTIT All INSERTIT user exits should follow this format: void INSERTIT( char*Rec, long* RecLen, short* Status ) { ... EXEC SQL insert ..... ; *Status = DTLInsertedResult( sqlstate ); } INSTRUCTIONS The INSTRUCTIONS exit is called when DataLoader/MX is preparing to terminate as a result of being given an invalid parameter.
Creating a Customized Version of DataLoader/MX NEWTRANSACTION The value of Status indicates where the modified record is located. If the modified record is in RecIn, set Status to 1. If the modified record is in RecOutBuf, set Status to 2. If MISCn does not modify the record, set Status to anything except 1 or 2. Both RecIn and RecOutBuf are buffers whose length is RecOutBufLen bytes long.
Creating a Customized Version of DataLoader/MX STATISTICSTIME STATISTICSTIME The STATISTICSTIME exit is called just after DataLoader/MX puts out its periodic statistics as a result of the -S parameter. Use it to display statistics unique to your user exits. The syntax is: void STATISTICSTIME( long long* InRecCount ) TERMINATING The TERMINATING exit is usually called when DataLoader/MX is terminating. If Normal is nonzero, DataLoader/MX is terminating in a normal fashion.
Creating a Customized Version of DataLoader/MX UPDATEIT If you use COBOL to write your user exits, the name of this procedure is T0351G10DEFAULTEXITS-COBOL. The syntax is: void T0351G10_DEFAULTEXITS_C( void ) UPDATEIT The UPDATEIT exit is called when you explicitly request it by specifying the letters u, U, h, or H in the list of user exits called through the -X parameter. Use it to update a row in the database.
Creating a Customized Version of DataLoader/MX Default User Exits Table 5-3. Default User Exits User Exit Description of Default Behavior BUILDKEY Returns the record itself (up to the first 255 bytes) as the key. CHECKARG Returns a value indicating that it does not recognize the argument passed. Running the default DataLoader/MX specifying an argument that DataLoader/MX itself does not recognize causes it to terminate with a brief description of the legal parameters.
Creating a Customized Version of DataLoader/MX DataLoader/MX Library DataLoader/MX Library Use the DataLoader/MX library of utility routines to make writing user exits simpler. With the exception of the User Messages routines, you can call the procedures from any language.
Creating a Customized Version of DataLoader/MX Data Conversion The syntax is: short DTLExternalToInternalDatetime ( char* ExternalDateTime, long long* InternalDateTime ); SQLCI LOAD/COPY require date-time data in a unique internal format. Moreover, the conversion is complex. Therefore, this procedure is particularly useful for a user exit doing date conversion that is eventually destined for SQLCI LOAD/COPY.
Creating a Customized Version of DataLoader/MX (Most significant) Bits 0-8 0 Bit 9 1 Bit 10 1 Bit 11 1 Bit 12 1 Bit 13 1 Bit 14 1 Bit 15 1 (Least significant) if if if if if if if input input input input input input input Data Conversion contains contains contains contains contains contains contains YYYY MM DD HH MM SS MSSSSS The internal form of a date or time requires 64 bits. SQLCI LOAD/COPY requires that the timestamp be passed to it in the internal form.
Creating a Customized Version of DataLoader/MX Data Conversion integer is 9,223,372,036,854,775,807. DataLoader/MX checks to make sure that Len is not greater than nine. DTLPackedDecimalToLong This procedure converts a packed decimal field into a 32-bit integer. The behavior is the same as DTLPackedDecimalToLongLong, except that the result is stored in a long field instead of long long. The range of the value is between -2,147,483,648 and 2,147,483,647.
Creating a Customized Version of DataLoader/MX Data Conversion Buf is a pointer to the record whose fields are to be converted. NumFields is the number of fields to be converted. Offsets is a pointer to NumFields 32-bit values that give the offsets (counting from zero) of the fields to be converted. FieldSizes is a pointer to NumFields 32-bit values that give the sizes of the fields whose offsets are in the corresponding position of Offsets.
Creating a Customized Version of DataLoader/MX Sequential I/O offsetof( input_record, c6[1] ), offsetof( input_record, c6[2] ) }; /* This array switched. as in the static DTLLEN holds the size of each of the fields to be Make sure these entries are in the same order offsets array.
Creating a Customized Version of DataLoader/MX • • • • • Sequential I/O $ \ = /G /E Otherwise, it is handled as an OSS file. The file is opened for either read or write access, depending upon AccessMode. If the string pointed to by AccessMode is an uppercase or lowercase letter r, the file is opened for read access. If the string pointed to by AccessMode is an uppercase or lowercase letter w, the file is opened for write access. This string must be null-terminated.
Creating a Customized Version of DataLoader/MX User Messages—C The value returned by this procedure is the actual number of bytes written. This value is usually the number of bytes requested to be written, but can be smaller, if writing to a file whose record length is smaller than Len, or greater, if the file is an unstructured file without the ODDUNST attribute and Len is odd. If the KEYRANGE interpretation is used for output, the DTLWrite utility always calls the BUILDKEY exit.
Creating a Customized Version of DataLoader/MX User Messages—C The syntax is: void DTLPromptUser( char* Answer, int AnswerSize, char* Format, ... Specify Answersize as the number of bytes available in Answer. Use it to prevent buffer overflow when accepting the response from the user. Use Format and the optional parameters following it to construct the message to be displayed. The message can include embedded \n characters to form a multiline message.
Creating a Customized Version of DataLoader/MX User Messages—COBOL The syntax is: void DTLWriteToErr( char* Format, ... ); If this is the first message written to the error file, a message is written to stderr (and to stdout if it is different from stderr) to indicate that errors are occurring. If the -Q argument is specified, the write to stderr is skipped, and if stdout is the same as stderr, the message is not written. This procedure does not increment the error count.
Creating a Customized Version of DataLoader/MX User Messages—COBOL DTLFATALERRCOB This procedure writes the message to stderr and to stdout (unless stdout is the same as stderr). It ignores -Q so that the message appears at least on stderr. After the message has been written, DTLFATALERRCOB calls the TERMINATING user exit, passing 0 as the argument. Then it closes the error file (the -E file or stderr). Finally, it stops the process with completion code 3. DTLFATALERRCOB does not return.
Creating a Customized Version of DataLoader/MX SQL Error Checking and Statistics Maintenance If this is the first message written to the error file, a message is written to stderr (and to stdout if it is different from stderr) to indicate that errors are occurring. If the -Q argument was specified, the write to stderr is skipped, and if stdout is the same as stderr, the message is not written. This procedure does not increment the error count.
Creating a Customized Version of DataLoader/MX • SQL Error Checking and Statistics Maintenance Determine the return value of the function, which is positive if the operation is successful and negative if the operation is unsuccessful The three procedures are very similar. They differ only in the error checks they make and thw statistics they update. DTLDeletedResult This procedure is designed to be called immediately after you execute an embedded SQL DELETE.
Creating a Customized Version of DataLoader/MX Generating Test Data Generating Test Data You can use DataLoader/MX, not only to load data from an existing system, but also to create test data. DataLoader/MX has several utility procedures that help create test data with these requirements: • • • You must be able to generate data in quantities equal to the quantity of real data.
Creating a Customized Version of DataLoader/MX Generating Test Data because of their repeatability. From now on, the word random is used to mean pseudorandom. DataLoader/MX provides two random-number-generating procedures. Each call to either procedure returns a random unsigned 32-bit number. Values are evenly distributed over the range of an unsigned 32-bit number (0 through 4,294,967,295). The two procedures differ in the number of values they return before repeating (known as the period).
Creating a Customized Version of DataLoader/MX Generating Test Data DTLRandUnsignedLong2Init This procedure optionally can be used to initialize DTLRandUnsignedLong2 to cause it to start at a different point in its sequence of random values. By default, DataLoader/MX initializes DTLRandUnsignedLong2 with the seed value 1305. If you want to receive different sets of random numbers in different runs, you can use DTLRandUnsignedLong2Init to give a different seed in each of your runs.
Creating a Customized Version of DataLoader/MX Generating Test Data Nonalphanumeric characters in the odometer value are not incremented: (000)000-0000 (000)000-0001 (000)000-0002 ... (999)999-9999 (000)000-0000 DTLIncOdometer This procedure increments the odometer pointed to by Odometer. The syntax is: long DTLIncOdometer( char* Odometer ); This Odometer must be a null-terminated character string.
Creating a Customized Version of DataLoader/MX Generating Test Data unique first name, last name combinations. If this is enough, you can eliminate the middle name column. If you need more names, you could make the middle column represent a middle initial, with one of 26 unique values. This step raises the number of unique combinations to 73,918,338 (421 * 26 * 6,753).
Creating a Customized Version of DataLoader/MX Generating Test Data Shuffle is a flag that indicates whether the contents of Name are to be returned in a randomized way by DTLCartGenerateField or whether they are to be returned in the order they are in the file. A nonzero value causes them to be returned in a randomized way.
Creating a Customized Version of DataLoader/MX Generating Test Data values as there are columns in the field. These fields must be as long as the longest possible value for that field, plus one byte for the terminating NULL. The syntax is: short DTLCartGenerateField( short Field, char* Coln, short Lenn, ... ); Field is a value from 1 through 10 that specifies the field to which the column is to be added. Coln is the variable that holds the result.
Creating a Customized Version of DataLoader/MX Sorting With DTLSort short DTLCARTGENERATE4COLFIELDCOB ( short field, char* Col1, short Len1, char* Col2, short Len2, char* Col3, short Len3, char* Col4, short Len4); short DTLCARTGENERATE5COLFIELDCOB ( short field, char* Col1, short Len1, char* Col2, short Len2, char* Col3, short Len3, char* Col4, short Len4, char* Col5, short Len5); Field is a value from 1 through 10 that specifies the field to which the column is to be added.
Creating a Customized Version of DataLoader/MX Sorting With DTLSort Upon return from this end of input call, DTLSort is ready to return the records in sorted order. Each call to DTLSort returns the next record in the memory pointed to by Buf, and the int pointed to by Len is set to the length of the record. After all records have been returned, the next call causes DTLSort to set the int pointed to by Len to -1, indicating that all sorted records have been returned.
Creating a Customized Version of DataLoader/MX Sorting With DTLSort ASC or ASCENDING directs DTLSort to sort in ascending order on this field. DESC or DESCENDING directs DTLSort to sort in descending order on this field. startcol is the starting column of the key field in bytes from the start of the record, counting from 1. endcol is the ending column of the key field. length is the number of bytes in the key field. type is the type of data the field contains: STRING Ordinary alphanumeric data.
Creating a Customized Version of DataLoader/MX Sorting With DTLSort (UPS nnn) If supplied, nnn is the amount of memory, in kilobytes, to use for the work space for User Process Sort (UPS). If omitted, the UPS feature is not enabled.
Creating a Customized Version of DataLoader/MX Miscellaneous Utility Procedures Miscellaneous Utility Procedures These procedures provide additional services. DTLHash1UnsignedLong This procedure generates a random 32-bit integer from the key passed. The syntax is: unsigned long DTLHash1UnsignedLong( void* Key, long Len ); DTLStrcmpi This procedure makes a case-insensitive comparison between two strings that are standard null-terminated C strings.
Creating a Customized Version of DataLoader/MX Miscellaneous Utility Procedures The syntax is: char* DTLStrncat( char* Str1, DTLLEN Str1Len, char* Str2, DTLLEN Str2Len ); DTLStrncmpi This procedure makes a case-insensitive comparison between two strings. The strings are standard NULL-terminated C strings. However, only the first Len bytes are compared. If Str1 is less than Str2, DTLStrncmpi returns a negative value. If they are equal, DTLStrncmpi returns 0.
Creating a Customized Version of DataLoader/MX Building a Customized Version of DataLoader/MX DTLToUpper This procedure changes a standard, null-terminated C string to uppercase letters. The syntax is: void DTLToUpper( char* String ); Building a Customized Version of DataLoader/MX After you have decided on default or customer user exits and the routines to use, you need to create a version of DataLoader/MX that includes them. A shell script, build.sh, simplifies this task.
Creating a Customized Version of DataLoader/MX Example compilation, the mxsqlc or mxsqlco preprocessing is done and followed by the mxCompileUserModule module compilation. Note. To perform a customized DataLoader build with the MX option and for DataLoader/MX utility functions to work properly, perform module definition file compilation immediately after DataLoader/MX installation.
Creating a Customized Version of DataLoader/MX Example 2. Run the build script. For example: $ /usr/tandem/dataloader/build.sh mysource.c mydataload MX In this example: /usr/tandem/dataloader is the directory in which DataLoader/MX was installed. mysource.c is the name of the modified source file. mydataload is the name of the new object file to be created. MX specifies that there is embedded sql in the input source file, and SQL/MX SQL compilation is required.
6 DataLoader/MX Examples This section provides examples to demonstrate DataLoader/MX. These examples represent the four basic loading scenarios, from which nearly all real loading scenarios are derived, as well as use with the file transfer protocol (FTP).
DataLoader/MX Examples Single Source Parallel Loading 4. At the same time, the second DataLoader/MX process for keyrange 2 reads a block of records, does any necessary data conversions, and waits for a read from its $RECEIVE file. 5. The import process for keyrange 2 reads a block of records from its DataLoader/MX process and then loads the partition with keyrange 2. Figure 6-1 illustrates this process. Figure 6-1.
DataLoader/MX Examples • • • Single Source Parallel Loading -I directs the DataLoader/MX process to read the input from the file infile. -O specifies that the output is written to the file kr, which is interpreted as a keyrange file. -S directs the DataLoader/MX process to generate a status message every 100,000 records. The file kr is a text file containing: "" "D" "G" ...
DataLoader/MX Examples Single Source Parallel Maintenance Command lines 6 through 9 start the import processes that read the converted and blocked records and do a load. Effectively, each import process loads a single partition of the table: 6$ import 7$ import 8$ import ... 9$ import cat.sch.mytab -ip \$dbl1 -u mytab.fmt & cat.sch.mytab -ip \$dbl2 -u mytab.fmt & cat.sch.mytab -ip \$dbl3 -u mytab.fmt & cat.sch.mytab -ip \$dbln -u mytab.
DataLoader/MX Examples Single Source Parallel Maintenance Figure 6-2. Single Source Parallel Maintenance Data Source $dbl0 Initial DataLoader/MX DBL1 DataLoader/MX 1 Part 1 DBL1 DataLoader/MX 2 Part 2 DBL1 DataLoader/MX N Part 3 VST011.vsd The following commands and their descriptions describe one way to accomplish the scenario in Figure 6-2. Note. gpri and cpu parameters are omitted from the example.
DataLoader/MX Examples • • • Multiple Source Parallel Loading -E gives each of these downstream DataLoader/MX processes different files (err1, err2, ...) in which to record their execution time errors. -I directs each DataLoader/MX process to get its input from the initial DataLoader/MX process, $dbl0. -X directs the processes to call a series of user exits indicated by the string “CIU”: ° ° “C” directs the process to call the user exit CONVERTIT.
DataLoader/MX Examples Multiple Source Parallel Loading The steps are: 1. The initial DataLoader/MX process for data source 1 reads a block of records from the input stream, looks at each record’s key, and sends it to the correct downstream DataLoader/MX process. 2. The DataLoader/MX process for keyrange 1 reads a block of records from its $RECEIVE file, does any necessary data conversions, and waits for a read from its import process. 3.
DataLoader/MX Examples Multiple Source Parallel Loading The following commands and their descriptions describe one way to implement the scenario in Figure 6-3. Note. gpri and cpu parameters have been omitted from the example. The initial DataLoader/MX process should run at high priority, and the downstream DataLoader/MX process and import pairs should run in the CPU where the primary disk process for the corresponding partition is running.
DataLoader/MX Examples • • • Multiple Source Parallel Maintenance -I directs processes to get their input from $RECEIVE, unblocking to 200-byte records. You did not specify a block size, so the DataLoader/MX process blocks as many records as fit into a 32,000-byte message. -X directs each DataLoader/MX process to call user exit CONVERTIT to do the data conversion. -O directs processes to use $RECEIVE as their output file.
DataLoader/MX Examples Multiple Source Parallel Maintenance Figure 6-4. Multiple Source Parallel Maintenance Data Source 1 Data Source M DBL1 DataLoader/MX 1 Part1 DBL1 DataLoader/MX 2 Part2 DBL1 DataLoader/MX 3 Part3 $dbl0 - initial DataLoader/MX 1 $dblm - Initial DataLoader/MX M DBL1 DataLoader/MX N PartN VST013.vsd These commands and their descriptions describe one way to accomplish the scenario in Figure 6-4. Note. gpri and cpu parameters are omitted from the example.
DataLoader/MX Examples • Multiple Source Parallel Maintenance -S directs the DataLoader/MX process to generate a status message every 100,000 records. The file kr is a text file containing: "" $dbl1 "D" $dbl2 "G" $dbl3 ... "Y" $dbln Because the DataLoader/MX process is instructed to interpret this output file as a keyrange file, the output records are not written to kr itself. Instead, the contents of kr, together with the key of each record, are used to determine where the records are written.
DataLoader/MX Examples ° Combined Usage With FTP If the insert is unsuccessful, "U" indicates that the next user exit, UPDATEIT, should be called to update the corresponding row in the database. If the update is successful, processing for that record is complete, and the process reads the next record. If the update is not successful, a message is sent to the error file indicating that no user exit was successful with this record, and the process reads the next record.
7 Recovery Strategies This section discusses strategies for planning DataLoader/MX run scenarios so that if you experience a failure during the load operation, you will be able to rerun DataLoader/MX and successfully load your database.
Recovery Strategies Design Time Considerations DataLoader/MX can be used to load a SQL/MX or SQL/MP database in many ways. It can be as simple as a single DataLoader/MX process putting data directly into a single table. It can be as complex as dozens, or hundreds, of DataLoader/MX processes arranged in multiple stages, with these processes taking input from multiple sources, processing it, and using it to load many different tables, some directly and some through import, LOAD, or APPEND.
Recovery Strategies Simple Recovery Approaches FTP to a disk from which you can load, rather than loading it directly from the FTP process. If you cannot reread the input data stream that was being processed when the failure occurred, you may not be able to craft a reliable failure recovery scenario. Your goal should be to construct a failure recovery plan that enables you to run the failed loading batch job again.
Recovery Strategies • Restarting From An Unknown State Restart from a known state. In this method, you bring the table to a known state and then rerun the load, starting from this known state. If the table began as an empty table, you would purge the table of data before restarting. If the table is small, it would be practical to copy it before you start, and then replace the target file with the copy if the load fails. Be sure to build this copy operation into your load procedure.
Recovery Strategies Restarting From An Unknown State have been deleted by the failed run. For updates, however, the situation is more complex. Updating Although many tables contain dates that pertain to the business use of the data, it usually is not possible to use this information to know at what record the failed load stopped performing updates.
Recovery Strategies Restarting From a Known State RECORD_NUM, also a 32-bit integer, and having the DataLoader/MX process count records, noting the number of the input record that the update came from: UPDATE SALESTABLE SET LOAD_ID = :CurLoadID, RECORD_NUM = :CurRecordNum, SALES_THIS_YEAR = SALES_THIS_YEAR + :SalesThisWeek WHERE LOAD_ID < :CurLoadID AND RECORD_NUM < :CurRecordNum AND PART_NUM = :PartNum While this scenario will work, it requires space for the RECORD_NUM column.
Recovery Strategies Batch Totals Purging the Restart File Before you purge the restart file, consider the way DataLoader/MX handles input and output. It is possible for DataLoader/MX to successfully end without processing all its input data. Suppose that a process is writing to DataLoader/MX by using the process name of the DataLoader/MX process. When that process terminates, DataLoader/MX receives an EOF on its $RECEIVE file, whether that process terminates normally or abnormally.
Recovery Strategies Multiprocess Considerations Multiprocess Considerations What is a failure in a situation where an application consists of multiple processes? If one process fails, should the others continue running? DataLoader/MX uses the strategy with the simplest restart state. If recovery is not simple and obvious, the DataLoader/MX process quits. This strategy propagates through the whole multiprocess application, terminating each process.
Recovery Strategies • Parallel Considerations Have the upstream DataLoader/MX process write records to the downstream DataLoader/MX processes. This method is not self-balancing. A slow downstream DataLoader/MX process can hold up distribution of records to all the other downstream DataLoader/MX processes. However, this method can be restarted if you use the method of restarting from a known state by using the -T parameter.
Recovery Strategies Parallel Considerations DataLoader/MX Reference Manual—525872-002 7- 10
A Error and Warning Messages This appendix lists the messages generated by DataLoader/MX along with cause and recovery information. Messages are grouped by the type of operation that produces them. Conversion Routine Messages DTLConvertToMilsAndMics: Invalid format for millisecond :millisec Cause. Invalid format was given for milliseconds. Recovery. Replace with correct format and restart DataLoader/MX. DTLExternalToInternalDatetime: Invalid DateTime format :datetime Cause.
Error and Warning Messages File System Messages Cause. The input buffer is too small for user exit listed. Recovery. Change the buffer size for the parameter listed and restart DataLoader/MX. File System Messages filename is full Cause. File is full; cannot continue. Recovery. Start DataLoader/MX with enough disk space. DTLAddedFileName: Error opening filename: error Cause. Cannot open filename with error Recovery. Check filename file and restart DataLoader/MX.
Error and Warning Messages File System Messages Recovery. Change the INT field description and restart DataLoader/MX. DTLBuildCSVFieldDesc: Missing char ( description.) Cause. The char is missing from the CSV field specified in description. Recovery. Modify the CSV fields specified and restart DataLoader/MX. DTLCheckFileAttrs: file is says it is $RECEIVE Cause. The specified file is $RECEIVE. Recovery. Change the file name and restart DataLoader/MX.
Error and Warning Messages File System Messages Recovery. Change the file type or change the modifier for the file and restart DataLoader/MX. DTLCheckIssueErrs: READX failed with error trying to read file Cause. The Guardian procedure call READX failed for the specified file. Recovery. Resolve the specified error for READX and restart DataLoader/MX. DTLCorrectKeyRangeOB: BUILDKEY returned an invalid status : status Cause. BUILDKEY exit has returned an invalid status. Recovery.
Error and Warning Messages File System Messages Recovery. Add in the NULL key entry in the keyrange file and restart DataLoader/MX. DTLGetFileAttrs: Invalid file name:file Cause. The specified file name is invalid. Recovery. Check the file name and restart DataLoader/MX. DTLGetFileAttrs: Error error doing FILE_GETINFOBYNAME_ on file Cause. An error from Guardian procedure call FILE_GETINFOBYNAME_ has been returned for the specified file. Recovery. Resolve the issue and restart DataLoader/MX.
Error and Warning Messages File System Messages Recovery. Resolve the issue and restart DataLoader/MX. DTLPrepareOutputCVSFile: WRITEEDIT returned error, file Cause. The Guardian procedure call WRITEEDIT has failed for the specified file. Recovery. Resolve the issue and restart DataLoader/MX. DTLProcessFeatures: Invalid interpretation or modifier: modifier Cause. The specified file modifier is invalid. Recovery.
Error and Warning Messages File System Messages Recovery. Modify the features accordingly and restart DataLoader/MX. DTLProcessFeatures: RECSIZE must be given for RECFORM=FB files Cause. RECSIZE was not specified with RECFORM=FB Recovery. Specify RECFORM=FB and restart DataLoader/MX. DTLProcessFeatures: Subtype modifier only allowed on $RECEIVE Cause. A subtype modifier was specified for files other than $RECEIVE Recovery. Remove the subtype from the file modifier and restart DataLoader/MX.
Error and Warning Messages File System Messages Recovery. Check the field description and data to make sure the length specified is long enough for data and restart DataLoader/MX. DTLReadCSV: Value out of range for a 1 byte int (data) Cause. The specified value is out of the one-byte INT range (0 and 256). Recovery. Change the data description field to accommodate the data and restart DataLoader/MX. DTLReadCSV: Value out of range for a 2 byte int (data) Cause.
Error and Warning Messages Partition Boundary Estimation Messages Recovery. Check the process name in -I= and -O= and restart DataLoader/MX. DTLWRITE: Invalid file number Cause. The file number passed into DTLWRITE is not correct. Recovery. Check the file number, which must be successfully returned from DTLOPEN and restart DataLoader/MX. DTLWrite: BUILDKEY returned illegal value :status Cause. The Status parameter in BUILDKEY is invalid. Recovery.
Error and Warning Messages $RECEIVE Messages $RECEIVE Messages DTLCheckForMsgs: Unexpected EOF on $RECEIVE Cause. DataLoader/MX was expecting data from $RECEIVE, but received an EOF. Recovery. Check the upper or lower streams of DataLoader/MX for abnormal termination and restart DataLoader/MX. DTLGetRecvInfo: Error error from FILE_GETRECEIVEINFO_ Cause. An error occured when calling the FILE_GETRECEIVEINFO_ Guardian procedure call. Recovery. Resolve the issue accordingly and restart DataLoader/MX.
Error and Warning Messages TMF Messages Recovery. Increase the Field parameter and restart DataLoader/MX. DTLCartAddCol: Field number too large Cause. The Field parameter passed to the DTLCartAddCol utility exceeds limits. The maximum is 10. Recovery. Decrease the Field parameter and restart DataLoader/MX. TMF Messages DTLBeginTransaction: BEGINTRANSACTION failed with error error Cause. The Guardian procedure call BEGINTRANSACTION failed with the error indicated. Recovery.
Error and Warning Messages TMF Messages Recovery. Resolve the issue and restart DataLoader/MX. DTLOpenTMFRestartFile: FILE_OPEN_ failed with error error Cause. The Guardian procedure call FILE_OPEN_ of the restart file specified in -T= parameter has failed with the error indicated. Recovery. Resolve the error and restart DataLoader/MX. DTLOpenTMFRestartFile: WRITEX failed with error error Cause. The Guardian procedure call WRITEX of the restart file has failed with the error indicated. Recovery.
Error and Warning Messages Miscellaneous Messages Miscellaneous Messages DTLInitialize: PROCESSHANDLE_GETMINE_ failed with error error Cause. The Guardian procedure call PROCESSHANDLE_GETMINE_ failed with the error indicated. Recovery. Resolve the issue accordingly and restart DataLoader/MX. DTLInitialize: INITIALIZE1 failed Cause. The user exit INITIALIZE1 returned status failed. Recovery. Check the return status of user exit INITIALIZE1. DTLInitialize: Expected '>', got char Cause.
Error and Warning Messages Warnings DataLoader/MX Reference Manual—525872-002 A -14
B Processing Flowcharts The diagrams in this appendix describe the processing flow for DataLoader/MX. Shaded boxes represent a processing step where one or more user exits can be called. Figure B-1 shows the overall flow of processing under DataLoader/MX. Figure B-1. Overall Processing Start 0 Initialize 1 Get next record EOF 2 Process record 3 Post process record Call to DTLFatalErr from anywhere 4 Normal termination 5 Abnormal termination End VST002.
Processing Flowcharts Figure B-2 shows how DataLoader/MX is initialized. Figure B-2. Initialization 0.0 Call EXITSDESCRIPTION 0.1 Write startup banner 0.2 Call INITIALIZE1 Call DTLFatalErr Fail 0.3 Process arguments Yes 0.5 Open them 0.4 -I or -O arguments? No 0.5 Call INITIALIZE2 Yes 0.7 Skip records Call SKIPPING Fail Call DTLFatalErr 0.
Processing Flowcharts Figure B-3 shows how DataLoader/MX processes arguments. Figure B-3. Argument Processing 0.30 Another argument? No Yes 0.32 Process it Yes 0.31 Is it known to DataLoader/ MX? No 0.33 Not known to DataLoader/MX Call CHECKARG Yes 0.34 Did CHECKARG recognize it? No 0.35 Print DataLoader/ MX usage instructions 0.36 Print user's add'l instructions Call INSTRUCTIONS 0.
Processing Flowcharts Figure B-4 shows DataLoader/MX getting the next record to process. Figure B-4. Getting Next Record 1.0 Record count (-C) reached Yes No 1.1 -G parameter? Yes No 1.2 Call GETNEXTRECORD Len = -1 1.3 Read input file EOF Legend Regular step End of input Step with exit call Note: NEXTINDIRECTFILE is called from box 1.3 if the INDIRECT interpretation is specified in the -I= parameter. VST005.
Processing Flowcharts Figure B-5 shows DataLoader/MX processing records. Figure B-5. Processing Records Yes 2.1 Begin transaction 2.0 Need to start TMF transaction? No 2.2 Call NEWTRANSACTION 2.3 -X parameter specified? No Yes 2.
Processing Flowcharts Figure B-6 shows DataLoader/MX processing user exits. Figure B-6. Processing User Exits 2.4.1 Call next user exit status =0 exit C1234 exit DIUH status >= 0 status <0 status >0 status <0 2.4.2 Dump record 2.4.3 Another exit? Yes No VST015.
Processing Flowcharts Figure B-7 shows DataLoader/MX post-record processing. Figure B-7. Post Record Processing 3.1 Status > 0? 3.2 -P parameter specified? Yes Yes No No 3.3 Call BUILDKEY 3.4 Partition boundary estimation algorithm 3.5 -O parameter specified? Yes 3.6 Write record to output file No 3.8 Call DONEWITHTRANSACTION Yes 3.9 End transaction 3.7 Time to commit a TMF transaction? No 3.11 Write DataLoader/MX standard statistics Yes 3.
Processing Flowcharts Figure B-8 shows DataLoader/MX normal termination. Figure B-8. Normal Termination 4.0 Write termination in progress message Yes 4.3 Call DONEWITHTRANSACTION 4.1 TMF transaction(s) active? No 4.2 End transaction 4.5 Wait until all data has been read 4.4 Yes $RECEIVE open for output? No 4.6 Write statistics 4.7 Call TERMINATING 4.8 Close all files Legend Regular step 4.9 Call TERMINATING2 Step with exit call STOP VST008.
Processing Flowcharts Figure B-9 shows DataLoader/MX abnormal termination. Figure B-9. Abnormal Termination 5.1 Write error message Yes 5.0 Error message provided? No 5.2 Call TERMINATING 5.
Processing Flowcharts DataLoader/MX Reference Manual—525872-002 B -10
C C-Only Error Functions This appendix describes C-only SQL error-checking functions. These functions work for C programs, but do not work for COBOL programs. Functions are retained for compatibility with earlier versions of DataLoader, but you should not use them for new customizations. Instead, use their replacements (DTLDeletedResult, DTLInsertedResult, and DTLUpdatedResult). (See SQL Error Checking and Statistics Maintenance on page 5-25 for descriptions of replacement functions.
C-Only Error Functions DataLoader/MX Reference Manual—525872-002 C- 2
Index Numbers 1 exit code 5-2 16-bit integer 5-17 2 exit code 5-2 3 exit code 5-2 32-bit integer 5-17, 5-38 32-bit unsigned integer 5-28 4 exit code 5-2 64-bit signed integer 5-16 A aaaindex file 2-1 Angle brackets versus parentheses 3-9 ASCII character string 5-14, 5-16 B Batch totals 7-7 Blocking 2-4 BROADCAST interpretation 4-2 Buffer pool 3-15 BUFSIZE modifier 4-9 BUILDKEY user exit default behavior 5-13 description of 5-4 build.
Index D dataload file 2-1 DataLoader/MP 1-3 DataLoader/MX applications 1-1 customizing 5-40 features 1-1 file package structure 2-1 file system 2-3/2-6 library 2-2, 5-14 main logic 2-2 process on NonStop system 1-1 reading and writing Guardian files 2-4 restart file 3-8 running 3-1/3-15 stderr file 3-3, 3-7 stdout file 3-7 structure 2-2 syntax 3-1 DATALOADER_OPTION 3-2 DATETIME 4-4, 5-14 Decimal fields, packed 5-16 Default user exits 5-12 DELETEIT user exit default behavior 5-13 description of 5-6 DONEWIT
Index E DTLWriteErr utility routine 5-22 DTLWRITEERRCOB utility routine 5-24 DTLWriteMsg utility routine 5-22 DTLWRITEMSGCOB utility routine 5-24 DTLWriteToErr utility routine 5-22 DTLWRITETOERRCOB utility routine 5-25 DTLWriteToUser utility routine 5-23 DTLWRITETOUSERCOB utility routine 5-25 DUMP interpretation 2-4, 4-6 E EBCDIC character string 5-14 interpretation 4-6 Edit file and key range output 6-3, 6-8 for CSV input 4-5 line numbering 2-5 Editor, fixing files with 4-6 Enscribe databases 1-1 file s
Index H H H exit code 5-3 I I exit code description of 5-3 example of 6-6 IBMVB form of RECFORM 4-12 import utility DataLoader/MX operation with 1-2, 1-3 input 2-6 processes 6-4, 6-9 INDIRECT interpretation 4-7 option 2-3 Indirect file names 3-10 INITIALIZE1 user exit default behavior 5-13 description of 5-7 INITIALIZE2 user exit default behavior 5-13 description of 5-8 Input stream 1-2 INSERTIT user exit default behavior 5-13 description of 5-8 example of 6-5, 6-6 INSTRUCTIONS user exit and -H parameter
Index M M MAX modifier 4-9 Messages conversion routine A-1 file system A-2 miscellaneous A-13 partition boundary estimation A-9 test data generation A-10 TMF A-11 user exit A-1 $RECEIVE A-10 Miscellaneous procedures 5-38 MISCn user exit default behavior 5-13 description of 5-9 Modifiers BUFSIZE 4-9 description of 4-1, 4-9 MAX 4-9 NOREWIND 4-10 NOUNLOAD 4-10 NUMBUFS 4-11 PERSIST 4-11 RECFORM 4-11 SUBTYPE 4-11, 4-12 TYPE 4-12 WAIT 4-13 % 4-10 Multiple source parallel data maintenance 6-9 parallel loading 6-
Index Q Parameters (continued) -Q 3-7, 3-12 -S 3-8, 5-2 -T 3-8, 5-2 -X 3-9, 5-2 Parentheses versus angle brackets 3-9 Partition boundary determination of 1-1, 3-11 estimation 3-7, 3-11, 5-4 estimation error messages A-9 example 6-1 keys 3-7 Percent modifier 4-10 Performance 2-5 PERSIST option of -I 4-11 pri and cpu parameters 6-2, 6-10 Procedures, miscellaneous 5-38 Processes nonexistent 2-6 opening 2-6 Q Quiet mode 3-7 R Random I/O 2-3 numbers 5-27 sampling 3-11 Read file 5-20 RECFORM modifier 4-11 Rec
Index U Test data (continued) description of 5-27 DTLCartAddCol 5-31 DTLCartAddColCob 5-32 DTLCartGenerate1ColFieldCOB 5-33 DTLCartGenerateField 5-32 DTLIncOdometer 5-30 DTLINCODOMETERCOB 5-30 DTLRandUnsignedLong 5-28 DTLRandUnsignedLong2 5-28 DTLRandUnsignedLong2Init 5-29 generating 5-27 generation error messages A-10 odometers 5-29 random numbers 5-27 TEXT interpretation 2-4, 4-9 TMF 3-8 TMF error messages A-11 Transaction Management Facility (TMF) 3-8 TYPE modifier 4-12 U U exit code description of 5-
Index V V VB form of RECFORM 4-12 W WAIT modifier 4-13 Warnings A-13 Write file 5-20 Special Characters "&" 3-9 #include command 2-1 $RECEIVE as input file, with PERSIST 4-11 description of 2-5 error messages A-10 % modifier 4-9, 4-10 -C parameter 3-3 -E parameter description of 3-3 example of error outfile 6-2, 6-5, 6-8 example of multiple error outfiles 6-3, 6-6, 6-8 syntax 3-12 -F parameter description of 3-3 SKIPPING exit 5-2, 5-10 -G parameter 3-3 -H parameter 3-4 -I parameter example of infile 6-3
