DataLoader/MP Reference Manual Abstract This manual describes the features and functionality of the DataLoader/MP and native mode (NM) DataLoader/MP products, tools to load HP NonStop™ SQL/MP databases. Product Version DataLoader/MP D41 NM DataLoader/MP D44 Supported Release Version Updates (RVUs) For DataLoader/MP, this publication supports D30.00 and all subsequent D-series RVUs, and G02.00 and all subsequent G-series RVUs until otherwise indicated by its replacement publication.
Document History Part Number Product Version Published 130776 DataLoader/MP D43 December 1996 142887 DataLoader/MP June 1998 424148-002 DataLoader/MP NM DataLoader/MP May 2002 424148-003 DataLoader/MP NM DataLoader/MP September 2003
DataLoader/MP 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 Additional Information x Notation Conventions x Tables vii ix 1. Introduction to DataLoader/MP DataLoader/MP Features 1-1 Uses for DataLoader/MP 1-1 Loading Scenario 1-2 Relationship to Other Loading Tools 1-3 2.
. Specifying File-Related Options for DataLoader/MP Contents 4. Specifying File-Related Options for DataLoader/MP Interpretations 4-2 BROADCAST 4-2 CSV 4-2 DUMP 4-5 EBCDIC 4-6 INDIRECT 4-7 KEYRANGE 4-8 Modifiers 4-9 BUFSIZE 4-9 MAX 4-9 NOREWIND 4-9 NOUNLOAD 4-10 num% 4-10 NUMBUFS 4-10 PERSIST 4-11 RECFORM 4-11 SUBTYPE 4-12 TYPE 4-12 WAIT 4-13 5.
6.
A. Error and Warning Messages Contents The Two Main Approaches 7-3 Restarting From An Unknown State 7-4 Restarting From a Known State 7-6 Batch Totals 7-7 Multi-Process Considerations 7-7 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.
Tables Contents Tables Table 2-1. Table 5-1. Table 5-2. Table 5-3.
Contents DataLoader/MP Reference Manual—424148-003 vi
What’s New in This Manual Manual Information DataLoader/MP Reference Manual Abstract This manual describes the features and functionality of the DataLoader/MP and native mode (NM) DataLoader/MP products, tools to load HP NonStop™ SQL/MP databases. Product Version DataLoader/MP D41 NM DataLoader/MP D44 Supported Release Version Updates (RVUs) For DataLoader/MP, this publication supports D30.00 and all subsequent D-series RVUs, and G02.
New and Changed Information What’s New in This Manual DataLoader/MP Reference Manual—424148-003 viii
About This Manual This manual describes DataLoader/MP software, which is used to load and maintain NonStop 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.
Additional Information About This Manual Section Title This section... 6 DataLoader/MP Examples Provides examples to demonstrate DataLoader/MP. These examples represent the four basic loading scenarios from which nearly all real loading scenarios are derived, as well as use with FTP.
General Syntax Notation About 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. For example: file-name [ ] Brackets. Brackets enclose optional syntax items. For example: TERM [\system-name.
Notation for Messages About This Manual Punctuation. Parentheses, commas, semicolons, and other symbols not previously described must be entered as shown. For example: error := NEXTFILENAME ( file-name ) ; LISTOPENS SU $process-name.#su-name Quotation marks around a symbol such as a bracket or brace indicate the symbol is a required character that you must enter as shown. For example: "[" repetition-constant-list "]" Item Spacing.
Change Bar Notation About This Manual be arranged either vertically, with aligned brackets on each side of the list, or horizontally, enclosed in a pair of brackets and separated by vertical lines. For example: LDEV ldev [ CU %ccu | CU %... ] UP [ (cpu,chan,%ctlr,%unit) ] { } Braces. A group of items enclosed in braces is a list of all possible items that can be displayed, of which one is actually displayed.
Change Bar Notation About This Manual DataLoader/MP Reference Manual—424148-003 xiv
1 Introduction to DataLoader/MP Dataloader/MP and native mode (NM) DataLoader/MP are software products that are used to load and maintain NonStop SQL/MP and Enscribe databases. Both products use the same syntax. NM Data/Loader/MP has additional capabilities that are noted in this manual when they occur. DataLoader/MP Features DataLoader/MP takes full advantage of the parallelism of the NonStop SQL/MP database product.
Loading Scenario Introduction to DataLoader/MP DataLoader/MP is a flexible product that lets you build loading applications quickly and can help with the challenges listed. It consists of a file processing program, DataLoader/MP, and a number of source files for load customization. DataLoader/MP can work in conjunction with the NonStop SQL/MP LOAD and APPEND utilities. Loading Scenario A typical load scenario might involve a load operation from a single input source into three partitions of a table.
Introduction to DataLoader/MP Relationship to Other Loading Tools Relationship to Other Loading Tools DataLoader/MP is designed to complement other tools that load and maintain NonStop SQL/MP databases. For example, the NonStop SQL/MP LOAD utility is not, in itself, parallel, but can be used in conjunction with DataLoader/MP to provide a parallel load solution. The SQLCI LOAD and APPEND commands require input in a specific format.
Introduction to DataLoader/MP Relationship to Other Loading Tools DataLoader/MP Reference Manual—424148-003 1 -4
2 DataLoader/MP Components This section describes the file package, structure, and file system of DataLoader/MP. DataLoader/MP File Package DataLoader/MP is delivered with the object file, DATALOAD, as well as edit files with prototypes for its utility procedures. You can copy and modify these procedures to control processing with your own version of DataLoader/MP.
DataLoader/MP Components DataLoader/MP Structure DataLoader/MP Structure Internally, DataLoader/MP consists of three major components - the DataLoader/MP main logic, user exits, and the DataLoader/MP library. Figure 2-1 shows the structure of DataLoader/MP. Figure 2-1. Structure of DataLoader/MP DataLoader/MP Main Logic User Exits Library VST001 The main logic component performs the basic program control.
DataLoader/MP Components DataLoader/MP File System build extremely large data file sets, consisting of many files. Using the INDIRECT option for input and output, you can go beyond the size limitation for single files. • • Full files. DataLoader/MP follows a specific strategy for file-full situations. When it encounters an error 43 or 45 on a file, DataLoader/MP writes a message to its home terminal and asks for the name of another file to use. This file can be on another disk.
DataLoader/MP Components DataLoader/MP File System write it as an OSS text file. DataLoader/MP still creates an Edit file if a Guardian file name being opened for output does not exist. The DataLoader/MP process itself must still be run in the Guardian environment, either from a TACL prompt or using gtacl from an OSS shell prompt. DataLoader/MP makes no attempt to determine whether an OSS file it is reading is a text file, so it does not treat “\n” characters as record separators.
DataLoader/MP Components • • DataLoader/MP File System Edit file line numbering. Whenever DataLoader/MP opens an edit file for output, it automatically changes the line increment to. 001, instead of to the default increment of 1.000. Consequently an empty edit file can contain 100,000,000 lines (.000, .001, .002, ..., 99999.999).
DataLoader/MP Components ° ° ° ° DataLoader/MP File System Output disk files with the VERIFYWRITES attribute Output ENSCRIBE files with alternate keys Input or output ENSCRIBE files with block sizes less than 4096 Unblocked interprocess communication DataLoader/MP Reference Manual—424148-003 2 -6
3 Running DataLoader/MP This section explains how to run DataLoader/MP. 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/MP most effectively. Syntax of DataLoader/MP To start DataLoader/MP, run the DataLoader/MP program and give it a list of parameters. These parameters, together with any user exits defined, determine what DataLoader/MP does.
Running DataLoader/MP Syntax of DataLoader/MP INDIRECT 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 MAX=size NOREWIND NOUNLOAD num% NUMBUFS=numbufs RECFORM= { FB<{RECSIZE=recsize [,PADCHAR=padchar]> } { VB[
Running DataLoader/MP • Syntax of DataLoader/MP Each parameter begins with a dash (-) and is immediately followed by the name of the parameter. If the parameter has a value, the value must follow the parameter name immediately, separated by an equal sign (=). If there is additional information 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.
Running DataLoader/MP • • • Syntax of DataLoader/MP If -I= is specified and -G is not specified, DataLoader/MP opens the input file and fetches the data to be processed. If -I= and -G are both specified, DataLoader/MP opens the input file but does not read data from it directly. When DataLoader/MP needs a record to process, it calls the GETNEXTRECORD exit. If you require it, GETNEXTRECORD can read the input file specified in -I= by calling DTLRead and passing -1 as the file number.
Running DataLoader/MP Syntax of DataLoader/MP 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/MP Syntax of DataLoader/MP -L=num-errs directs DataLoader/MP to terminate if the number of error messages reaches numerrs errors. If this parameter is not specified, DataLoader/MP continues regardless of the number errors detected. -O= [out-file ] [ (o-mod [, o-mod]...)] specifies an output file to write, with optional modifiers or interpretations, or both.
Running DataLoader/MP Syntax of DataLoader/MP 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 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/MP Syntax of DataLoader/MP Use this parameter to reduce the number of messages on the home terminal when running many DataLoader/MP processes simultaneously. When an error occurs, sometimes an earlier informational message can be helpful in understanding the error. If you use -Q, it is generally a good idea to specify an OUT file to capture all messages.
Running DataLoader/MP Considerations—DataLoader/MP Process file with a record size of 20. When DataLoader/MP successfully commits a transaction, it writes the last number of records read from input to this file. If an error occurs, this file contains the record number that was committed successfully. When DataLoader/MP is restarted with this restart file, the previously processed records (as recorded in the restart file) are skipped.
Running DataLoader/MP Considerations—DataLoader/MP Process filename [(feature [,feature]...])] specifies the Guardian file name including node name, volume, and subvolume or DEFINE name if necessary, or an OSS file name, followed by an optional list of features enclosed in parentheses and separated by commas. If a file name contains any “/” characters, NM DataLoader/MP will assume it is an OSS file name. The order of the features is not important.
Running DataLoader/MP Default Exit Examples Even if you have a very large amount of data, DataLoader/MP can help you. Suppose you have so much data that there is too much to sort, even if you just sort the keys. You can use the % modifier to randomly sample the data. One option is to randomly sample from the beginning of the data by using the parameters in this command: 1>DATALOAD -I=infile(1%, MAX=100000) -P=10 Random sampling selects 1% of the records and stops when 100,000 records have been selected.
Running DataLoader/MP Parallelism >DATALOAD -t=100 Directs DataLoader/MP to set the number of records per TMF transaction to 100 and to create a restart file named xyz >DATALOAD -t=100 -t=200 Produces an error because the TMF parameter is given twice >DATALOAD -I=batch1 -C=1000 & -F=100 Directs DataLoader/MP to skip the first 100 records, then process 1000 records >DATALOAD /OUT $d.
Running DataLoader/MP Taking Advantage of Parallelism Taking Advantage of Parallelism When parallelism exists, whether is has been created by using the methods described previously or is the result of having multiple, simultaneous input streams, DataLoader/MP can continue to process in parallel.
Running DataLoader/MP Analyzing Your Configuration experiment with this method. For example, you can start with one data source and one DataLoader/MP process feeding into multiple downstream processes. You may discover that if you use two or more DataLoader/MP processes for input, your load becomes better balanced and throughput improves. • Inter-process communication (IPC) costs. When splitting the load by adding processes, be aware of the increased IPC costs.
4 Specifying File-Related Options for DataLoader/MP DataLoader/MP has two types of file-related options: interpretations and modifiers. An interpretation specifies the overall manner in which the contents of a file is to be 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/MP Interpretations Interpretations An interpretation specifies the overall manner in which the contents of a file are to be handled. If you do not specify an interpretation, 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.
Specifying File-Related Options for DataLoader/MP 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/MP CSV length is the length of the field. length is required for CHAR and INT field types and is not allowed for DATETIME, which is always eight bytes long. For INT type fields, length must be one, two, four, or eight bytes. The total translation information is a comma-separated list of these individual field translation specifications, with one specification for each comma-separated data value, in the order of the comma-separated data values.
Specifying File-Related Options for DataLoader/MP DUMP and are ignored. If you use the CONTCHAR, it replaces the ampersand (&). See record 2. • • • • • CHAR fields do not have to be enclosed within quotes. The comma (or the SEPCHAR) signals the end of the field. See record 1. A CHAR field may be shorter than its specification. The value is left-justified and blank-padded in the internal record. See record 1.
Specifying File-Related Options for DataLoader/MP EBCDIC Each character in a record in a DUMP file is displayed once in hexadecimal format and, if it is a printable character, again in ASCII. If the character is not printable, a period (.) is printed. When changing records in a DUMP file, the use designator allows you to avoid changing both the hexadecimal and ASCII versions of the characters. If use is HEX, only the hexadecimal representation of the record is used to reconstruct the record.
Specifying File-Related Options for DataLoader/MP INDIRECT INDIRECT The INDIRECT interpretation directs the file system to treat the contents of the file as a list of file names. These file names can themselves have DataLoader/MP features. This interpretation can be used in several ways: • Use a group of files as input by creating an edit file called FILE1 that would look like this: $DATA1.ARDATA.MYFILE1 $DATA1.ARDATA.MYFILE2 $DATA1.ARDATA.
Specifying File-Related Options for DataLoader/MP KEYRANGE DEFINE names begin with an equals sign (=). If you try to use a DEFINE name for -E, -I, -O, -S, or -T, the command line would contain a double equals sign: -I==defname. Double equals signs is TACL’s symbol for start of a comment, which would cause the command to be misinterpreted. To use a DEFINE name with -E, -I, -O, -S, or -T, you must place a tilde (~) before the double equals: -I~==defname.
Specifying File-Related Options for DataLoader/MP Modifiers Modifiers Modifiers are features that change the way the DataLoader/MP file system handles the contents of the file, but not in such a fundamental way as interpretations do. Unlike interpretations, any number of modifiers can be used for a specific file. BUFSIZE BUFSIZE specifies the size of the I/O buffers to be allocated for the file. It has the following format: BUFSIZE=num BUFSIZE is permitted for unstructured disc files (except edit files).
Specifying File-Related Options for DataLoader/MP NOUNLOAD NOUNLOAD The NOUNLOAD modifier is allowed only for tape files. It specifies that the tape is to be rewound to the load point and left online when DataLoader/MP closes it. If DataLoader/MP terminates abnormally, it ignores the NOUNLOAD modifier. This modifier is available only in NM DataLoader/MP. num% The % modifier specifies the percent of records that are to be processed. num can be from 0.000 to 100.000, inclusive.
Specifying File-Related Options for DataLoader/MP PERSIST PERSIST If $RECEIVE is your input or output file, you can use the PERSIST option. With this option, DataLoader/MP does not terminate when the number of openers falls to 0 (zero) but waits for additional opens.
Specifying File-Related Options for DataLoader/MP SUBTYPE 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. The format of the block is the same as that used by FUP when specifying the VARIN or VAROUT option. The format of vbspecs is [BLOCKSIZE=num] where num is the size of the blocks.
Specifying File-Related Options for DataLoader/MP WAIT DataLoader/MP process must appear to the COBOL library as a tape drive (device type 4). Enter the job stream as: 1 >DATALOAD /nowait,name $dbl0/ -E=err0 -I=$RECEIVE (TYPE=4, & SUBTYPE=10) 2 >cobprog /nowait/ -E=err1 -O=$dbl0 By using these values, $dbl0 appears to the COBOL program to be a 5188 tape drive (type=4, subtype=10). For interprocess communication, DataLoader/MP is limited by the NonStop Kernel to 32,000-byte block I/O.
Specifying File-Related Options for DataLoader/MP DataLoader/MP Reference Manual—424148-003 4- 14 WAIT
5 Creating a Customized Version of DataLoader/MP Although DataLoader/MP’s default functions might be enough for the loading scenario you anticipate, it is more likely that you will need to create a customized version of DataLoader/MP. 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/MP User Exits Table 5-1.
Creating a Customized Version of DataLoader/MP 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/MP User Exit Descriptions User Exit Descriptions The interfaces to user exits are designed so your code can be written in C or COBOL85. 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 following descriptions describe the C prototypes. The SEXITSCB file, described on page 2-1, contains COBOL models.
Creating a Customized Version of DataLoader/MP CHECKARG CHECKARG The CHECKARG exit is called if DataLoader/MP is given a parameter that is not recognized by DataLoader/MP itself. The syntax for this procedure follows: void CHECKARG( char* Arg, long* ArgLen, short* Status ) This exit provides a way to pass parameters to your other user exits. When you process a parameter that you recognize, save whatever information from it is needed in your global variables so it is accessible to your other user exits.
Creating a Customized Version of DataLoader/MP 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 for this procedure follows: void DELETEIT( char*Rec, long* RecLen, short* Status ) If the deletion is successful, DELETEIT should return a positive value.
Creating a Customized Version of DataLoader/MP EXITSDESCRIPTION EXITSDESCRIPTION The EXITSDESCRIPTION exit is called to obtain a string that is descriptive of the user exits. This string is printed in DataLoader/MP’s startup banner.
Creating a Customized Version of DataLoader/MP INITIALIZE2 The syntax for this procedure follows: void INITIALIZE1( short* Status ) If initialization is successful, move a non-zero value to Status. If initialization fails, move a zero to Status. If you return a 0 value, DataLoader/MP will terminate abnormally. If you want to display messages about the cause of the initialization failure, you can call DTLWriteErr to display a message, then return with 0 in Status.
Creating a Customized Version of DataLoader/MP • • INSTRUCTIONS Updating DataLoader/MP’s internal statistics Returning a value appropriate for the Status return from INSERTIT. For this reason, all INSERTIT user exits should follow this format: void INSERTIT( char*Rec, long* RecLen, short* Status ) { ... EXEC SQL insert .....
Creating a Customized Version of DataLoader/MP NEWTRANSACTION Status should be set to 2. If MISCn does not modify the record, Status should be set to anything except 1 or 2. Both RecIn and RecOutBuf are buffers whose length is RecOutBufLen bytes long. It is permissible to extend the record, even when converting in place in RecIn, as long as the new length is less than RecOutBufLen bytes. If MISCn fails, it should return a value of -1 in Status.
Creating a Customized Version of DataLoader/MP STATISTICSTIME STATISTICSTIME The STATISTICSTIME exit is called just after DataLoader/MP puts out its periodic statistics as a result of the -S parameter. It allows statistics unique to your user exits to be displayed. The syntax for this procedure follows: void STATISTICSTIME( long long* InRecCount ) TERMINATING The TERMINATING exit is usually called when DataLoader/MP is terminating. If Normal is nonzero, DataLoader/MP is terminating in a normal fashion.
Creating a Customized Version of DataLoader/MP T7900D41_DEFAULTEXITS_C T7900D41_DEFAULTEXITS_C T7900V00-DEFAULTEXITS-COBOL This exit is present only in the nonnative mode version of DataLoader/MP. This exit is not an exit in the normal sense in that it is never called, and, if by mistake it is called, it does nothing. Its purpose is to assist in determining what user exits are bound into a given DataLoader/MP object file.
Creating a Customized Version of DataLoader/MP Default User Exits For this reason, all UPDATEIT user exits should follow this format: void UPDATEIT( char*Rec, long* RecLen, short* Status ) { ... EXEC SQL update ..... ; *Status = DTLUpdatedResult( &sqlcode, &sqlca ); } Default User Exits DataLoader/MP includes a set of default user exits, listed in Table 5-3. Table 5-3.
Creating a Customized Version of DataLoader/MP DataLoader/MP Library Table 5-3. Default User Exits (page 2 of 2) User Exit Description of Default Behavior NEXTINDIRECTFILE Does nothing. SKIPPING Does nothing. STATISTICSTIME Does nothing. TERMINATING Does nothing. Returns a value indicating that it executed correctly. UPDATEIT Terminates with a message that it has been called unexpectedly.
Creating a Customized Version of DataLoader/MP Data Conversion The syntax for this procedure follows: void DTLEBDCICToASCII( char* EBCDIC, long Len ); DTLExternalToInternalDatetime This procedure converts a character string containing a date and time to the internal form suitable for processing by SQLCI LOAD. The syntax for this procedure follows: short DTLExternalToInternalDatetime ( char* ExternalDateTime, long long* InternalDateTime ); SQLCI LOAD requires date-time data in an unique internal format.
Creating a Customized Version of DataLoader/MP Data Conversion The value returned by this procedure indicates the outcome of the conversion, where the following indicate an error condition: -1 The input is syntactically incorrect -2 The input is ambiguous -3 The day or date does not exist, such as the 30th of February If none of these errors occur, the result is a positive number composed of seven bits indicating which of the seven possible components were actually present, as follows: (Most signifi
Creating a Customized Version of DataLoader/MP Data Conversion 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/MP Data Conversion The syntax for this procedure follows: void DTLSwitchEndian (char* Buf, long* NumFields, DTLLEN* Offsets, DTLLEN* FieldSizes); 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.
Creating a Customized Version of DataLoader/MP Sequential I/O */ 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. Make sure these entries are in the same order as in the offsets array.
Creating a Customized Version of DataLoader/MP Sequential I/O Name must be null-terminated and must contain either a Guardian file name or an OSS file name. If Name contains any “/” characters, DataLoader/MP treats it as an OSS file name. If Name does not contain any “/” characters, DataLoader/MP treats it as a Guardian file name and the usual default volume rules apply. OSS files are treated as if they were Guardian unstructured files. No interpretation may be specified for an OSS file.
Creating a Customized Version of DataLoader/MP User Messages—C If -2 is specified in FileNum, 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. 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.
Creating a Customized Version of DataLoader/MP User Messages—C DTLPromptUser This procedure writes a message to the process’ home terminal, then accepts input from the user. Answersize should be specified as the number of bytes available in Answer. It is used to prevent buffer overflow when accepting the response from the user. Format and the optional parameters following it are used to construct the message to be displayed. The message may include embedded \n characters to form a multi-line message.
Creating a Customized Version of DataLoader/MP User Messages—COBOL DTLWriteToErr This procedure writes the message to the process’ error file (the file specified by -E or stderr if -E was not specified). If this is the first message written to the process’ error file, a message is written to stderr (and to stdout if it is different from stderr) to announce that errors are occurring.
Creating a Customized Version of DataLoader/MP User Messages—COBOL The syntax for this procedure follows: short DTLCONTINUECOB(char* Question, short* QuestionLen ); 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).
Creating a Customized Version of DataLoader/MP SQL Error Checking and Statistics Maintenance DTLWRITEMSGCOB This procedure writes the message to the process’ error file (the file specified by -E or stderr if -E was not specified) and to stdout if it is different from stderr. If this is the first message written to the process’ error file, a message is written to stderr (and to stdout if it is different from stderr) to announce that errors are occurring.
Creating a Customized Version of DataLoader/MP SQL Error Checking and Statistics Maintenance operation was successful and, if so, how many rows were affected.
Creating a Customized Version of DataLoader/MP Generating Test Data Generating Test Data DataLoader/MP is useful not only for loading data from an existing system, but also for creating test data. DataLoader/MP has several utility procedures that help create test data. There are three requirements for test data: • • • You must be able to generate data in quantities equal to the quantity of real data.
Creating a Customized Version of DataLoader/MP Generating Test Data because of their repeatability. From now on, the word “random” will be used to mean pseudo-random. DataLoader/MP 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/MP 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/MP 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/MP Generating Test Data Non-alphanumeric 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 for this procedure follows: long DTLIncOdometer( char* Odometer ); This odometer must be a null-terminated character string.
Creating a Customized Version of DataLoader/MP Generating Test Data 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). If this is not enough, you can duplicate the last name field in the middle name field, giving 6,753 middle names and 19,198,866,788 (421 * 6,753 * 6,753) unique combinations.
Creating a Customized Version of DataLoader/MP Generating Test Data randomized way. This strategy is useful because, although it is much easier to maintain files with lists when the lists are in some order, you would not usually want that order to be carried over into the test data. There can be a maximum of 10 Cartesian fields, and each Cartesian field can have a maximum of 10 columns. DTLCARTADDCOLCOB This procedure adds a column to a Cartesian field. It can be called from COBOL programs.
Creating a Customized Version of DataLoader/MP Generating Test Data The syntax for this procedure follows: 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. Lenn is the maximum number of characters that may be placed in Coln. Specify a Col and Len pair for each column in the result.
Creating a Customized Version of DataLoader/MP Sorting With DTLSort 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. Coln is the variable that holds the result. Lenn is the maximum number of characters that may be placed in Coln. Specify a Col and Len pair for each column in the result.
Creating a Customized Version of DataLoader/MP Sorting With DTLSort call also sets the memory pointed to by Buf to information about the sort. This information is in a 44 byte structure so, for this reason, the memory pointed to by Buf when retrieving sorted records should be at least 44 bytes long. Upon return from this end of output call, DTLSort is again in the uninitialized state, and the whole sequence can be repeated if another sort is to be done.
Creating a Customized Version of DataLoader/MP Sorting With DTLSort 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/MP Sorting With DTLSort For example: asc 2:4 string, asc 5 for 4, desc 10 for 4 integer describes the most significant key as a character string in columns 2 through 4 to be sorted in ascending order; the second key as a character string (the default type) in columns 5 through 8, again to be sorted in ascending order; and the least significant key as a 4-byte binary integer in columns 10 through 13 to be sorted in descending order.
Creating a Customized Version of DataLoader/MP Miscellaneous Utility Procedures Miscellaneous Utility Procedures The following procedures provide additional services. DTLHash1UnsignedLong This procedure generates a pseudo-random 32-bit integer from the key passed. The syntax for this procedure follows: unsigned long DTLHash1UnsignedLong( void* Key, long Len ); DTLStrcmpi This procedure makes a case-insensitive comparison between two strings. The strings are standard null-terminated C strings.
Creating a Customized Version of DataLoader/MP Miscellaneous Utility Procedures The syntax for this procedure follows: 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.
Creating a Customized Version of DataLoader/MP The MAKE Routine for NM DataLoader/MP DTLToUpper This procedure changes a standard, null-terminated C string to uppercase letters. The syntax for this procedure follows: void DTLToUpper( char* String ); The MAKE Routine for NM DataLoader/MP After you have decided on the default or custom user exits and routines you need to use, you will need to create a version of DataLoader/MP that includes them. A TACL routine, MAKE, simplifies this task.
Creating a Customized Version of DataLoader/MP Example steps identifies the steps necessary to create the new object file. Valid codes are: L Link the user exits in oexits with the standard DataLoader/MP code and any default exits needed to create a runnable object file. LS Perform the link as above, then SQL-compile the new object file. new specifies the name of the new object file that is to be created. oexits specifies the name of an object file containing custom user exits.
Creating a Customized Version of DataLoader/MP The MAKE Routine for Nonnative Mode DataLoader/MP The MAKE Routine for Nonnative Mode DataLoader/MP To use the MAKE routine with the Nonnative mode version of DataLoader/MP, use this syntax: MAKE steps old-DATALOAD exitsobjfile new-DATALOAD steps identifies the steps necessary to create the new object file.
Creating a Customized Version of DataLoader/MP Example $SYSTEM.DLSOURCE.MAKE is the subvolume into which DataLoader/MP was installed. BA indicates that the users exits in exitsobjfile should be bound with the contents of old-DATALOAD, replacing the user exits in old-DATALOAD with those from exitsobjfile, producing a new object file that should be accelerated. old-DATALOAD is the unmodified version object file. myobject is the object file with your custom exits.
Creating a Customized Version of DataLoader/MP DataLoader/MP Reference Manual—424148-003 5- 44 Example
6 DataLoader/MP Examples This section provides examples to demonstrate DataLoader/MP. These examples represent the four basic loading scenarios from which nearly all real loading scenarios are derived, as well as use with FTP.
DataLoader/MP Examples Single Source Parallel Loading 3. The SQLCI load process for keyrange 1 reads a block of records from its DataLoader/MP process then does a PARTONLY load of the partition with keyrange 1. 4. At the same time, the second DataLoader/MP process for keyrange 2 reads a block of records, does any necessary data conversions, and waits for a read from its $RECEIVE file. 5.
DataLoader/MP Examples Single Source Parallel Loading assumes that the key of the record is the first 255 bytes of the record, which is not the case with this data. 1>dbl0 /NOWAIT/ -E=err0 -I=infile -O=kr(keyrange) -S=100000 The preceding command uses these parameters: • • • • -E specifies that any execution time errors are recorded in the file named err0. -I directs the DataLoader/MP process to read the input from the file infile.
DataLoader/MP Examples Single Source Parallel Maintenance 4>dbl1 /NOWAIT, NAME $dbl3/ -E=err3 & -I=$RECEIVE(recform=fb) -X=c -O=$RECEIVE ... 5>dbl1 /NOWAIT, NAME $dbln/ -E=errn & -I=$RECEIVE(recform=fb) -X=c -O=$RECEIVE Command lines 6 through 9 start the SQLCI processes that read the converted and blocked records and do a PARTONLY load so that each record goes into its appropriate partition.
DataLoader/MP Examples Single Source Parallel Maintenance Figure 6-2. Single Source Parallel Maintenance Data Source $db10 Initial DataLoader/MP DBL1 DataLoader/MP 1 Part 1 DBL1 DataLoader/MP 2 Part 2 DBL1 DataLoader/MP N Part 3 VST011 The following commands and their descriptions describe one way to accomplish the scenario in Figure 6-2. Note. PRI and CPU parameters are omitted from the example.
DataLoader/MP Examples Multiple Source Parallel Loading The following commands use these parameters: • • • -E gives each of these downstream DataLoader/MP processes different files (err1, err2, ...) in which to record their execution time errors. -I directs each DataLoader/MP process to get its input from the initial DataLoader/MP 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/MP Examples Multiple Source Parallel Loading format required by SQLCI (SQLUTIL) LOAD and performs any other processing that might be necessary. You can use any number of routines to do this conversion. The steps that DataLoader/MP performs are as follows: 1. The initial DataLoader/MP 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/MP process. 2.
DataLoader/MP Examples Multiple Source Parallel Loading Figure 6-3.
DataLoader/MP Examples Multiple Source Parallel Loading The file kr is an edit file with these contents: "" "D" "G" ... "Y" $dbl1 $dbl2 $dbl3 $dbln Because the DataLoader/MP process was 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/MP Examples Multiple Source Parallel Maintenance PARTONLY; EXIT ... 11>SQLCI /NOWAIT/ LOAD $dbln,=partn, RECIN 215, BLOCKIN 31820, PARTONLY; EXIT Multiple Source Parallel Maintenance This highly parallel load scenario with write operations uses only two simple exits: one to build the key from a raw record (the BUILDKEY exit in the initial DataLoader/MP process) and one to convert a raw record to a suitable format (the CONVERT exit in downstream DataLoader/MP processes).
DataLoader/MP Examples Multiple Source Parallel Maintenance The following commands and their descriptions describe one way to accomplish the scenario in Figure 6-4. Note. PRI and CPU parameters are omitted from the example. The initial DataLoader/MP processes should run at high priority, and the downstream DataLoader/MP processes should be distributed across processors.
DataLoader/MP Examples Combined Usage With FTP error file indicating that no user exit was successful with this record, and the process reads the next record. 3>dbl1 /NOWAIT/ -X=ciu 4>dbl1 /NOWAIT/ -X=ciu 5>dbl1 /NOWAIT/ -X=ciu ...
7 Recovery Strategies This section discusses strategies for planning DataLoader/MP run scenarios so that you will be able to rerun DataLoader/MP and successfully load your database if you experience a failure during the load operation. Why Is Recovery Important? The contents of a database are crucial. To ensure that data is valid, it must be loaded accurately, whatever its source. Loading a database can be a one-time activity or part of a daily, weekly, or monthly job schedule.
Recovery Strategies Design Time Considerations Design Time Considerations You should consider how you will load your database at the time you design it, along with other considerations, such as query performance and online transaction processing performance. Along with those considerations, consider how you will recover if the load process fails. You must ensure that DataLoader/MP will be able to read the data again, possibly in the same order as it read it on its first run.
Recovery Strategies Simple Recovery Approaches prepare for this rerun. Avoid creating a situation in which you must create a completely different set of complex batch jobs to recover from the failure. Simple Recovery Approaches In some cases, simple techniques can be used to recover a table from a failed load. If the table started out empty, remove any data left by the failed load and restart DataLoader/MP.
Recovery Strategies Restarting From An Unknown State You must decide which approach best fits your application, taking into consideration the time required to do the rerun and the computing resources that either approach will need. Restarting From An Unknown State To use this method, you must know or determine whether a row was altered by the failed load. If you have a way of determining this, you may be able to use restarting from an unknown state.
Recovery Strategies Restarting From An Unknown State system, the date column will not necessarily be a reliable indicator of when the table or record was last updated. You can set up a system in which data is segregated by date into separate loads so that you can tell when the table or record was last updated.
Recovery Strategies Restarting From a Known State 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. Finally, you can create a load program that accepts a parameter that indicates whether it is a rerun or not, and the load program executes different code each case.
Recovery Strategies Batch Totals Suppose a process is writing to DataLoader/MP using the process name of the DataLoader/MP process. When that process terminates, DataLoader/MP will receive an end of file on its $RECEIVE file whether that process terminated normally or abnormally. DataLoader/MP then ends normally even though it has not read all of the input data, which will make a rerun necessary. If you have purged the restart file, you will not be able to rerun DataLoader/MP.
Recovery Strategies Parallel Considerations DataLoader/MP process quits. This strategy propagates through the whole multiprocess application, terminating each process. When the error involves data in the records DataLoader/MP is processing, DataLoader/MP logs the record together with the error to its error log file specified with the -E parameter.
Recovery Strategies Parallel Considerations In order for a loading job to be restarted using restarting from a known state and the -T parameter, each DataLoader/MP process must receive the same records and in the same order as were read in the first run.
Recovery Strategies Parallel Considerations DataLoader/MP Reference Manual—424148-003 7- 10
A Error and Warning Messages This appendix lists the messages that relate to DataLoader/MP along with their cause and any recovery steps that should be taken. 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/MP. 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/MP. File System Messages filename is full Cause. File is full; cannot continue. Recovery. Start DataLoader/MP with enough disk space. DTLAddedFileName: Error opening filename: error Cause. Cannot open filename with error Recovery. Check filename file and restart DataLoader/MP.
Error and Warning Messages File System Messages Recovery. Change the INT field description and restart DataLoader/MP. DTLBuildCSVFieldDesc: Missing char ( description.) Cause. The char is missing from CSV field specified in description. Recovery. Modify the CSV fields specified and restart DataLoader/MP. DTLCheckFileAttrs: file is says it is $RECEIVE Cause. The specified file is $RECEIVE. Recovery. Change the file name and restart DataLoader/MP.
Error and Warning Messages File System Messages Recovery. Change the file type or change the modifier for the file and restart DataLoader/MP. 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/MP. 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/MP. DTLGetFileAttrs: Invalid file name:file Cause. The specified file name is invalid. Recovery. Check the file name and restart DataLoader/MP. 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/MP.
Error and Warning Messages File System Messages Recovery. Resolve the issue and restart DataLoader/MP. DTLPrepareOutputCVSFile: WRITEEDIT returned error, file Cause. The Guardian procedure call WRITEEDIT has failed for the specified file. Recovery. Resolve the issue and restart DataLoader/MP. 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/MP. DTLProcessFeatures: RECSIZE must be given for RECFORM=FB files Cause. RECSIZE was not specified with RECFORM=FB Recovery. Specify RECFORM=FB and restart DataLoader/MP. 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/MP.
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/MP. DTLReadCSV: Value out of range for a 1 byte int (data) Cause. The one-byte INT must be between 0 and 256. Recovery. Change the data description field to accommodate the data and restart DataLoader/MP. 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/MP. 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/MP. 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/MP was expecting data from $RECEIVE, but received an EOF. Recovery. Check the upper or lower streams of DataLoader/MP for abnormal termination and restart DataLoader/MP. DTLGetRecvInfo: Error error from FILE_GETRECEIVEINFO_ Cause. An error occured when calling FILE_GETRECEIVEINFO_ Guardian procedure call. Recovery. Resolve the issue accordingly and restart DataLoader/MP.
Error and Warning Messages TMF Messages Recovery. Increase the Field parameter and restart DataLoader/MP. DTLCartAddCol: Field number too large Cause. The Field parameter passed into DTLCartAddCol utility exceeds limits. The maximum is 10. Recovery. Decrease the Field parameter and restart DataLoader/MP. 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/MP. 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 specified. Recovery. Resolve the error and restart DataLoader/MP. DTLOpenTMFRestartFile: WRITEX failed with error error Cause. The Guardian procedure call WRITEX of the restart file has failed with the error specified. 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/MP. 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/MP Reference Manual—424148-003 A -14
B Processing Flowcharts The diagrams in this Appendix describe the processing flow for DataLoader/MP. 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/MP. Figure B-1.
Processing Flowcharts Figure B-2 shows how DataLoader/MP 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/MP 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/ MP? No 0.33 Not known to DataLoader/MP Call CHECKARG Yes 0.34 Did CHECKARG recognize it? No 0.35 Print DataLoader/ MP usage instructions 0.36 Print user's add'l instructions Call INSTRUCTIONS 0.
Processing Flowcharts Figure B-4 shows DataLoader/MP 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 Legend Regular step EOF End of input Step with exit call Note: NEXTINDIRECTFILE would be called from box 1.3 if the INDIRECT interpretation were specified in the -I= parameter.
Processing Flowcharts Figure B-5 shows DataLoader/MP 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/MP processing user exit. Figure B-6. Processing User Exits 2.4.1 Call next user exit status =0 exit C1234 exit DIUA status >= 0 status <0 status <0 status >0 2.4.2 Dump record 2.4.
Processing Flowcharts Figure B-7 shows DataLoader/MP’s 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/MP standard statistics Yes 3.
Processing Flowcharts Figure B-8 shows DataLoader/MP’s 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 Yes 4.4 $RECEIVE open for output? No 4.6 Write statistics 4.7 Call TERMINATING Legend 4.
Processing Flowcharts Figure B-9 shows DataLoader/MP’s 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/MP Reference Manual—424148-003 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. They are retained for compatibility with earlier versions of DataLoad, 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 those functions.
C-Only Error Functions DataLoader/MP Reference Manual—424148-003 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-38 32-bit unsigned integer 5-28 4 exit code 5-3 64-bit signed integer 5-16 A Angle brackets versus parentheses 3-9 APPEND SQLCI command 1-3 ASCII character string 5-14, 5-16 B Blocking 2-4 BROADCAST interpretation 4-2 BUFSIZE modifier 4-9 BUILDKEY user exit default behavior 5-13 description of 5-4 Busy files 2-5 C C exit code description of 5-3 example of 6-6, 6-11 C language case conversion 5-39, 5-4
Index E DTLCARTGENERATEnCOLFIELDCOB utility routine 5-33 DTLClose utility routine 5-19 DTLContinue utility routine 5-21 DTLCONTINUECOB utility routine 5-23 DTLDeletedResult utility routine 5-26 DTLEBDICToASCII utility routine 5-14 DTLExternalToInternalDatetime utility routine 5-15 DTLFatalErr utility routine 5-21 DTLFATALERRCOB utility routine 5-24 DTLHash1UnsignedLong utility routine 5-38 DTLIncOdometer utility routine 5-30 DTLINCODOMETERCOB utility routine 5-30 DTLInsertedResult utility routine 5-26 DTL
Index F Exit codes 5-2 EXITSDESCRIPTION user exit default behavior 5-13 description of 5-7 T0330U00_DEFAULTEXITS_C 5-11, 5-12 F FB form of RECFORM 4-11 Files closing 5-19 editing line numbering 2-5 fixing with editor 4-6 full 2-3 indirect names of 3-10 numbering 2-5 opening 2-5, 5-19 reading 5-20 restarting 7-6 source code 2-1, 5-41 system error messages A-2 writing 5-20 FTP example 6-12 G GETNEXTRECORD user exit and -G parameter 5-2 default behavior 5-13 description of 5-7 H H exit code 5-3 I Indire
Index L L N LOAD SQLCI command 1-3 Loading tools, other 1-3 Lowercase letters, converting to 5-39 NEWTRANSACTION user exit default behavior 5-13 description of 5-10 NEXTINDIRECTFILE user exit default behavior 5-14 description of 5-10 NOREWIND modifier 4-9 NOUNLOAD modifier 4-10 Numbering, edit file line 2-5 Numbers, random 5-27 NUMBUFS modifier 4-10 M MAKE routine 2-1, 5-40 MAX modifier 4-9 Messages conversion routine A-1 file system A-2 miscellaneous A-13 partition boundary estimation A-9 test data g
Index R Parameters (continued) -S 3-8, 5-2 -T 3-8, 5-2 -X 3-9, 5-2 Parentheses versus angle brackets 3-9 Partition boundary estimation 3-7, 3-10, 5-4 estimation error messages A-9 example 6-1 Percent modifier 4-10 Performance 2-4 PERSIST option of -I 4-11 Procedures, miscellaneous 5-38 Processes, opening 2-5 R Random numbers 5-27 Read file 5-20 RECFORM modifier 4-11 S Sequential I/O (SIO) 5-19 SEXITSC file 2-1, 5-41 SEXITSCB file 2-1, 5-4, 5-41 Single source parallel data maintenance 6-4 Single source p
Index U U U exit code description of 5-3 example of 6-6, 6-11 Unstructured files 2-3 UPDATEIT user exit default behavior 5-14 description of 5-12 example of 6-5, 6-6, 6-11 Uppercase letters, converting to 5-40 Usage statistics report 6-4 User exits BUILDKEY 5-4, 5-13 CHECKARG 5-2, 5-5, 5-13 CONVERTIT 5-5, 5-13 customized 5-40 default 5-1, 5-13 DELETEIT 5-6, 5-13 DONEWITHTRANSACTION 5-6, 5-13 error messages A-1 exit codes 5-2 EXITSDESCRIPTION 5-7, 5-11, 5-12, 5-13 GETNEXTRECORD 5-2, 5-7, 5-13 INITIALIZE1 5
Index Special Characters -E parameter description of 3-3 example of error outfile 6-3, 6-5, 6-8, 6-11 example of multiple error outfiles 6-3, 6-6, 6-9 syntax 3-11 -F parameter 3-3, 5-2, 5-10 -G parameter 3-3 -H parameter 3-4 -I parameter example of infile 6-3, 6-5, 6-8, 6-11 example of process infile 6-6, 6-11 example of $RECEIVE infile 6-3, 6-9 PERSIST option 4-11 syntax 3-4 -L parameter 3-6 -O parameter description of 3-6 example of CSV 4-5 example of keyrange file 6-3, 6-8 example of output $RECEIVE 6-
Index Special Characters DataLoader/MP Reference Manual—424148-003 Index -8
