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 H01 Supported Release Version Updates (RVUs) This publication supports H06.03 and all subsequent H-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 543544-001 DataLoader/MX H01 November 2006
DataLoader/MX Reference Manual Glossary Index Figures 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 xiii Tables 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.
3. Running DataLoader/MX (continued) Contents 3. Running DataLoader/MX (continued) Analyzing Your Configuration 3-15 4. Specifying File-Related Options for DataLoader/MX Interpretations 4-2 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.
. Creating a Customized Version of DataLoader/MX (continued) Contents 5.
7. Recovery Strategies (continued) Contents 7. Recovery Strategies (continued) Restarting From An Unknown State 7-4 Restarting From a Known State 7-6 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-11 TMF Messages A-11 Miscellaneous Messages A-13 Warnings A-13 A-9 B.
Tables Contents Tables Table 2-1. Table 5-1. Table 5-2. Table 5-3.
Contents DataLoader/MX Reference Manual—543544-001 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 H01 Supported Release Version Updates (RVUs) This publication supports H06.03 and all subsequent H-series RVUs until otherwise indicated by its replacement publication.
Changes to the G06.
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 is 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.
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.
Change Bar Notation 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.
Change Bar Notation About This Manual The CRE has many new message types and some new message type codes for old message types. In the CRE, the message type SYSTEM includes all messages except LOGICAL-CLOSE and LOGICAL-OPEN.
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—543544-001 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 There are two primary considerations in a parallel load or maintenance scenario: • • 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 Building Your Loading Application Building Your Loading Application A typical loading problem cannot be solved by running a single DataLoader/MX process. Instead, loading is performed by an application consisting of multiple DataLoader/MX processes, some customized, others standard, and perhaps one or more import processes. Configuring and tuning your loading application are discussed next.
Running DataLoader/MX Analyzing Your Configuration Analyzing Your Configuration After your configuration is defined, you should analyze and measure it for performance by running DataLoader/MX and analyzing the results of a few simple experiments. For most configurations, the statistics provided through the -S= parameter should be sufficient. However, you can also use Measure and other performance tools.
Running DataLoader/MX Analyzing Your Configuration DataLoader/MX Reference Manual—543544-001 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 Interpretations Interpretations Specify an interpretation to control how the contents of a file are to be handled. Otherwise, each read moves a bit-for-bit copy of the next record into the program’s memory, or on output, places a copy of the record in the program’s memory into the file. If you do specify an interpretation, the file system does not merely move the bit pattern into, or, out of, the program’s memory.
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 (&). For more information, 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—543544-001 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 lists exits called only if you specify a parameter that requires them to be called. Table 5-1.
Creating a Customized Version of DataLoader/MX User Exits Table 5-2. Parameter -X Exit Codes (page 2 of 2) 3 MISC3 Continue with next exit. Dump record to -E file. Read next record. Read next record. 4 MISC4 Continue with next exit. Dump record to -E file. Read next record. Read next record. 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.
Creating a Customized Version of DataLoader/MX User Exit Descriptions User Exit Descriptions The interfaces to user exits are designed so that your code can be written in C or COBOL. Exits have uppercase names, they accept all parameters by reference, and if they return a value indicating how they functioned, it is returned in a parameter named Status. The C prototypes are described next. The sexitscb.cob file, described on page 2-1, contains COBOL models.
Creating a Customized Version of DataLoader/MX CHECKARG CHECKARG The CHECKARG exit is called if DataLoader/MX is given a parameter that is not recognized by DataLoader/MX itself. 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.
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 T0351H01DEFAULTEXITS-COBOL. The syntax is: void T0351H01_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 Default User Exits DataLoader/MX includes a set of default user exits, listed in Table 5-3. Table 5-3. Default User Exits (page 1 of 2) 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.
Creating a Customized Version of DataLoader/MX DataLoader/MX Library Table 5-3. Default User Exits (page 2 of 2) User Exit Description of Default Behavior TERMINATING Does nothing. Returns a value indicating that it executed correctly. TERMINATING2 Does nothing. Returns a value indicating that it executed correctly. UPDATEIT Terminates with a message that it has been called unexpectedly. DataLoader/MX Library Use the DataLoader/MX library of utility routines to make writing user exits simpler.
Creating a Customized Version of DataLoader/MX Data Conversion DTLExternalToInternalDatetime This procedure converts a character string containing a date and time to the internal form suitable for processing by SQLCI LOAD. 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.
Creating a Customized Version of DataLoader/MX Data Conversion The value returned by this procedure indicates the outcome of the conversion, where an error condition is indicated by: -1 The input is syntactically incorrect. -2 The input is ambiguous. -3 The day or date does not exist (for example, the 30th of February).
Creating a Customized Version of DataLoader/MX Data Conversion The syntax is: short DTLPackedDecimalToLongLong( char* PD, long Len, long long* Result ); The value returned by this procedure indicates the outcome of the conversion. A value of 1 means that the conversion was successful. A value of 0 (zero) means that the input string was not a valid packed decimal field. A value of -1 indicates that the input is potentially too large to fit in a 64-bit integer.
Creating a Customized Version of DataLoader/MX Data Conversion DTLSwitchEndian This procedure converts big-endian fields to little-endian fields (or the reverse), in place. Use this procedure when importing or exporting binary data from or to a system whose byte order for binary fields is different from the byte order of the system running DataLoader/MX.
Creating a Customized Version of DataLoader/MX Sequential I/O long long c5; short c6[3]; } input_record; input_record* i_r; /* This array holds the offset from the beginning of the record of each field to be switched. */ static DTLLEN offsets[] = { offsetof( input_record, c2 ), offsetof( input_record, c3 ), offsetof( input_record, c5 ), offsetof( input_record, c6[0] ), offsetof( input_record, c6[1] ), offsetof( input_record, c6[2] ) }; /* This array holds the size of each of the fields to be switched.
Creating a Customized Version of DataLoader/MX Sequential I/O DTLOpen This procedure opens the file whose name, together with any requested features, is pointed to by Name. The syntax is: short DTLOpen( char* Name, char* AccessMode ); Name must be null-terminated and must contain either an OSS file name or a Guardian file name. If Name starts with one of these characters, DataLoader/MX handles it as a Guardian file: • • • • • $ \ = /G /E Otherwise, it is handled as an OSS file.
Creating a Customized Version of DataLoader/MX User Messages—C There are no error returns. Errors are handled by DataLoader/MX. DTLWrite This procedure writes up to Len bytes from the buffer pointed to by Buf to the file FileNum. The syntax is: long DTLWrite( short FileNum, char* Buf, long Len ); If FileNum is -2, the data is written to the file specified in the -O= parameter. The value returned by this procedure is the actual number of bytes written.
Creating a Customized Version of DataLoader/MX User Messages—C passing 0 as the argument. Then it closes the error file (the -E file or stderr). Finally, it calls exit(3). DTLFatalErr does not return. Note. Having calls to DTLFatalErr from the TERMINATING user exit is dangerous because it can result in recursive function calls. The syntax is: void DTLFatalErr( char* Format, ... ); DTLPromptUser This procedure writes a message to the process stderr and then accepts input from the user.
Creating a Customized Version of DataLoader/MX User Messages—C The syntax is: void DTLWriteMsg( 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 User Messages—COBOL This procedures can be used to communicate with the user. Note. There are two versions of each User Messages routine: one is designed for use with C, and the other is designed for use with COBOL. Two versions exist because, although it is common practice for C routines that print messages to accept a variable parameter list, COBOL cannot call routines that have variable parameter lists.
Creating a Customized Version of DataLoader/MX User Messages—COBOL The user response is placed into Answer. The number of characters accepted is placed into AnswerLen. void DTLPROMPTUSERCOB(char* Answer, int* AnswerSize, int*AnswerLen, char* Msg, int* MsgLen); DTLWRITEERRCOB This procedure writes the message to the process error file (the file specified by -E or stderr if -E was not specified).
Creating a Customized Version of DataLoader/MX SQL Error Checking and Statistics Maintenance The syntax is: void DTLWRITETOERRCOB(char* Msg, short* MsgLen ); DTLWRITETOUSERCOB This procedure writes the message to stderr and to stdout (unless stdout is the same as stderr). If the -Q parameter is specified, the write to stderr is skipped, and if stdout is the same as stderr, the message is not be written. This procedure does not increment the error count.
Creating a Customized Version of DataLoader/MX Generating Test Data DTLInsertedResult This procedure is designed to be called immediately after you execute an embedded SQL INSERT. It checks the outcome, updates DataLoader/MX internal statistics, and returns a value of 1 if the insert was successful and a value of -1, otherwise.
Creating a Customized Version of DataLoader/MX Generating Test Data Reports designed with this data would look very different when generated with real data. A client tool looks and operates differently when used with nonrepresentative data. To use DataLoader/MX to create test data, replace the default GETNEXTRECORD user exit with one that generates data, using some of the procedures below. This GETNEXTRECORD builds a record consisting of fields that each have one type of test data.
Creating a Customized Version of DataLoader/MX Generating Test Data distributions. There are, however, other useful distributions, most notably the Normal distribution, which can be generated from an even distribution. DTLRandUnsignedLong This procedure returns the next unsigned 32-bit number from a pseudo-random series with a period of 1,073,741,824.
Creating a Customized Version of DataLoader/MX Generating Test Data 00002 ... 99999 00000 For situations where you desire such test data, DataLoader/MX provides a utility procedure, DTLIncOdometer, to create it. DTLIncOdometer, however, generalizes on the odometer concept by letting the values be alphabetic as well as numeric. For example, these sequences are supported by DTLIncOdometer: AAAA AAAB AAAC ... ZZZZ AAAA aaaa aaab aaac ... zzzz aaaa Odometer values can also be mixed, as shown: aAA0 aAA1 aAA2 .
Creating a Customized Version of DataLoader/MX Generating Test Data This procedure returns a value 0, if the odometer has reached its maximum value and DTLIncOdometer has started to return duplicate combinations. A nonzero value indicates that the result is not a repeat of the first combination. DTLINCODOMETERCOB This procedure can be called from a COBOL program. It is the same as the DTLIncOdometer, except that the Len parameter provides the length of parameter Odometer.
Creating a Customized Version of DataLoader/MX Generating Test Data DataLoader/MX includes a number of data files you can use with Cartesian field procedures. The file data/aaaindex in the DataLoader/MX directory contains a short description of each data file. You are not limited to using data files included with DataLoader/MX. You can use data files you create yourself. Use Cartesian fields to control the distribution of values in a field.
Creating a Customized Version of DataLoader/MX Generating Test Data Field is a value from 1 through 10 that specifies the field to which the column is to be added. Name is the name of a file that contains the values the column will contain, with one value per record. Len is the length of the Name parameter. 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 appear in the file.
Creating a Customized Version of DataLoader/MX Generating Test Data If DTLCartGenerateField returns 0, it has started to return duplicate combinations. A nonzero value indicates that the result is not a repeat of prior combinations. DTLCARTGENERATE[1-5]COLFIELDCOB These procedures are COBOL-callable routines for generating Cartesian fields. Because COBOL does not accept variable numbers of parameters, there are five routines, for one to five columns in a field.
Creating a Customized Version of DataLoader/MX Sorting With DTLSort Specify a Col and Len pair for each column in the result. DataLoader/MX moves a maximum of Lenn bytes to its corresponding Coln column. For a field with n columns added through DTLCartAddCol, use DTLCARTGENERATEnCOLFIELDCOB. Sorting With DTLSort This procedure provides a very simple interface for sorting. The syntax is: void DTLSort( char* Buf, int* Len ); Initially DTLSort is in the unintialized state.
Creating a Customized Version of DataLoader/MX Sorting With DTLSort The format of the key description that must be passed on the first call to DTLSort is: key-desc [, key-desc ]...
Creating a Customized Version of DataLoader/MX Sorting With DTLSort length is the number of bytes in the key field. type is the type of data the field contains: STRING Ordinary alphanumeric data. UPPER Alphanumeric data, but lowercase characters are compared as if they were uppercase. INTEGER Signed binary data. REAL Floating point data. UNSIGNED Unsigned binary data.
Creating a Customized Version of DataLoader/MX Miscellaneous Utility Procedures specified, the job gets its speed enhanced by UPS. If the records do not all fit in the work space, FastSort transparently falls back to the normal method of sorting. To request UPS with a 100 KB memory work space for the previous example, use: asc 2:4 string, asc 5 for 4, desc 10 for 4 integer (UPS 100) The sort statistics returned by the last call to DTLSort are described by this structure defined in dtload.
Creating a Customized Version of DataLoader/MX Miscellaneous Utility Procedures The syntax is: short DTLStrcmpi( char* Str1, char* Str2 ); DTLStrdup This procedure creates a duplicate of a standard, null-terminated C string. It is the caller’s responsibility to free this duplicate string when it is no longer needed. The syntax is: char* DTLStrdup( char* String ); DTLStrlastchr This procedure returns a pointer to the last occurrence of a given character in a standard null-terminated C string.
Creating a Customized Version of DataLoader/MX Building a Customized Version of DataLoader/MX DTLStrndup This procedure creates a duplicate of the first Len bytes of String, which is a standard, null-terminated C string. It is the caller’s responsibility to free this duplicate string when it is no longer needed.
Creating a Customized Version of DataLoader/MX Building a Customized Version of DataLoader/MX build.sh can also SQL compile the customized object file (required if your custom user exits contain embedded SQL). To use the build.sh routine with DataLoader/MX, use this syntax: build.sh user_exits [outfile [MX|MP]] user_exits is the name of the user exits source or object file. The file extension must be one of: • • • .c (C source) .cbl or .cob (COBOL source) .
Creating a Customized Version of DataLoader/MX Example DataLoader/MX installation directory can be specified by setting the environment variable DL_INSTALL_DIR to point to the installation directory. For example: $ export DL_INSTALL_DIR="/usr/opt/dataloader" You can set the environment variable DL_COMP_OPTION to specify options that need to be passed to the c89 or ecobol language compiler during source compilation.
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, in addition to 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 on page 6-7. 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 processor 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 In this discussion, loading refers to loading new information into the database in the third way. Some of the new information takes the form of new rows, some takes the form of updated rows, and some takes the form of deleted rows. 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.
Recovery Strategies Simple Recovery Approaches For example, data in a disk file can easily be read again. However, if the data is being received by FTP from another system, when you rerun the load after a failure, the data might no longer be available, or might not be available in the same order. In that case, you might need to add a step to the normal loading scenario to send the data by using FTP to a disk from which you can load, rather than loading it directly from the FTP process.
Recovery Strategies • • Restarting From An Unknown State Restarting from an unknown state. In this method, you leave the modifications made by the failed load in the table but devise a way for the rerun load to recognize, on a row-by-row basis, whether the action it is preparing to take has already been done by a previously failed run. For more information, see Parallel Considerations on page 7-8 for a description of self-balancing parallel configurations. Restart from a known state.
Recovery Strategies Restarting From An Unknown State Inserting and Deleting If the load consists of inserting new rows, you can use the restarting from an unknown state method. Your program can work its way through the file, attempting to insert rows. If the insert fails because of a duplicate key, the row was inserted by the failed run of this same load job. The program can continue until it is able to insert a row without error, and then insert the rest of the rows. The same is true of deletes.
Recovery Strategies Restarting From a Known State At successful completion of the load job, the program increments the LOAD_ID in the parameter file or table. The LOAD_ID field can serve another purpose. It enables you to query the database to determine which rows were and were not altered in the latest load run. Generally, restarting from an unknown state is not the best recovery strategy when multiple updates to a record can occur in a single load job.
Recovery Strategies Batch Totals file. If it exists, DataLoader/MX reads the restart file, positions itself in the input file to the record number of the input file that is recorded in the restart file, and starts normal processing with the first record after the last one in the last committed transaction. If the restart file does not exist, DataLoader/MX creates the restart file and starts processing with the first record in the input.
Recovery Strategies Multiprocess Considerations But if you intend to use batch totals to summarize the change that the job made to the database or to summarize how much of the input data failed to update the database, that simple approach will not give valid totals. You might need to use the approach of including a LOAD_ID column in the database, as described under Restarting From An Unknown State on page 7-4.
Recovery Strategies • • Parallel Considerations Have the downstream DataLoader/MX processes read from the upstream DataLoader/MX process. This method is self-balancing because each process can get another block of records as soon as it has finished its current block of records.
Recovery Strategies Parallel Considerations DataLoader/MX Reference Manual—543544-001 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 Cause. The INT field description specified for comma-separated value (CSV) was not the correct length: 1, 2, 4, or 8 bytes. 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.
Error and Warning Messages File System Messages Cause. The specified file is a non-EDIT file with dump interpretation. 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.
Error and Warning Messages File System Messages Cause. There is no NULL key entry in the specified keyrange file. 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.
Error and Warning Messages File System Messages Cause. The Guardian procedure call POSITION has failed for the specified file. 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 Cause. An invalid feature was specified for file. 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.
Error and Warning Messages File System Messages Cause. The record listed in CSV file is too long. 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.
Error and Warning Messages Partition Boundary Estimation Messages Cause. DataLoader/MX tried to read or write to its own process, which is not allowed. 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.
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 Test Data Generation Messages Test Data Generation Messages DTLCartAddCol: Field number too small Cause. The Field parameter passed to the DTLCartAddCol utility is less than 0. 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.
Error and Warning Messages TMF Messages Cause. The Guardian procedure call GETTRANSID returned the error indicated. Recovery. Resolve the specified error and restart DataLoader/MX. DTLOpenTMFRestartFile: Restart file already open Cause. The restart file specified in -T= parameter is opened by another process. Recovery. Resolve the issue and restart DataLoader/MX. DTLOpenTMFRestartFile: FILE_OPEN_ failed with error error Cause.
Error and Warning Messages Miscellaneous Messages Cause. The Guardian procedure call RESUMETRANSACTION failed with the error indicated. Recovery. Resolve the error and restart DataLoader/MX. DTLGetCharFromString: Invalid character specification Cause. A specified character was not printable. Recovery. Replace the string with a printable character and restart DataLoader/MX. Miscellaneous Messages DTLInitialize: PROCESSHANDLE_GETMINE_ failed with error error Cause.
Error and Warning Messages Warnings Warnings message: Enter C to continue or S to stop > Cause. There may be a problem as stated in the message. Enter C to continue or S to stop the run of DataLoader/MX. Recovery. No recovery is needed; this is only a warning.
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—543544-001 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). For more information, see SQL Error Checking and Statistics Maintenance on page 5-26 for descriptions of replacement functions.
C-Only Error Functions DataLoader/MX Reference Manual—543544-001 C- 2
Index Numbers 1 exit code 5-2 16-bit integer 5-17 2 exit code 5-2 3 exit code 5-3 32-bit integer 5-17, 5-39 32-bit unsigned integer 5-29 4 exit code 5-3 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 DataLoader/MP 1-3 DataLoader/MX applications 1-1 customizing 5-41 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-15 Decimal fields, packed 5-16 Default user exits 5-13 DELETEIT user exit default behavior 5-13 description of 5-6 DONEWITHTRANSACTION user
Index E DTLWRITEERRCOB utility routine 5-25 DTLWriteMsg utility routine 5-22 DTLWRITEMSGCOB utility routine 5-25 DTLWriteToErr utility routine 5-23 DTLWRITETOERRCOB utility routine 5-25 DTLWriteToUser utility routine 5-23 DTLWRITETOUSERCOB utility routine 5-26 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 system 2-3, 2-5 files 1-3 FILE_OPE
Index I 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 5-2 default behavi
Index N Messages (continued) user exit A-1 $RECEIVE A-10 Miscellaneous procedures 5-39 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-6 Multiprocess considerations 7-8 N Naming files 3-10 NEWTRANSACTION user exit default behavior 5-13 description of 5-10 NEXTINDIRECT
Index Q Partition boundary (continued) 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-39 Processes nonexistent 2-6 opening 2-6 Q Quiet mode 3-7 R Random I/O 2-3 numbers 5-28 sampling 3-11 Read file 5-20 RECFORM modifier 4-11 Recovery strategies 7-1/7-9 RUN_OPTION 3-2 S Sequential I/O (SIO) 5-19 sexitscb.cob file 2-1 sexitsc.c file 2-1 sexitsc.
Index U Test data (continued) DTLRandUnsignedLong2Init 5-30 generating 5-28 generation error messages A-11 odometers 5-30 random numbers 5-28 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-3 example of 6-6 Unstructured files 2-3 UPDATEIT user exit default behavior 5-14 description of 5-12 example of 6-5, 6-6 Uppercase letters, converting to 5-41 Usage statistics report 6-4 User exits BUILDKEY 5-4, 5-
Index Special Characters #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, 6-5, 6-8 example of process infile 6-6 example of $RECEIVE infile 6-3, 6-9 PERSIST opti
