“Democracy is a process by which people are free to choose the man who will get the blame.”

- Laurence J. Peter

When data is being loaded into Teradata the processing of the data is performed by the utility. All of the utilities are able to read files that contain a variety of formatted and unformatted data. They are able to read from disk and from tape. These files and devices must support a sequential access method. Then, the utility is responsible for incorporating the data into SQL for use by Teradata. However, there are times when it is advantageous to use a different access technique or a special device.

When special input processing is desired, then an INMOD (acronym for INput MODule) is a potential approach to solving the problem. An INMOD is written to perform the input of the data from a data source. It removes the responsibility of performing input data from the utility. Many times an INMOD is written because the utility is not capable of performing the particular input processing. Other times, it is written for convenience.

The INMOD is a user written routine to do the specialized access from the file system, device or database. The INMOD does not replace the utility; it becomes a part of and an extension of the utility. The major difference is that instead of the utility receiving the data directly; it receives the data from the INMOD. An INMOD can be written to work with FastLoad, MultiLoad, TPump and FastExport.

As an example, an INMOD might be written to access the data directly from another RDBMS besides Teradata. It would be written to perform the following steps:

1. Connect to the RDBMS
2. Retrieve a row using a SELECT or DECLARE CURSOR
3. Pass the row to the utility
4. Loop back and do steps 2 & 3 until there is no more data
5. When there is no more data, disconnect from the RDBMS

An INMOD is sometimes called an exit routine. This is because the utility exits itself by calling the INMOD and passing control to it. The INMOD performs its processing and exits back as its method for passing the data back to the utility. The following diagram illustrates the normal logic flow when using the utility:

As seen in the above diagram, there is an extra step involved with the processing of an INMOD. On the other hand, it can eliminate the need to create an intermediate file by literally using another RDBMS as its data source. However, the user still scripts and executes the utility, like when using a file, that portion does not change. The following chart shows the appropriate languages for mainframe and network-attached systems: written in.

As shown in the diagrams above, the user still executes the utility and the utility is responsible for calling the INMOD. Therefore, the utility needs an indication from the user that it is supposed to call the INMOD instead of reading a file.

Normally the utility script contains the name of the file or JCL statement (DDNAME). When using an INMOD, the file designation is no longer specified. Instead, the name of the program to call is defined in the script.

The following chart indicates the appropriate statement to define the INMOD:

The writing of an INMOD is primarily concerned with processing an input data source. However, it cannot do the processing haphazardly. It must wait for the utility to tell it what and when to perform every operation.

It has been previously stated that the INMOD returns data to the utility. At the same time, the utility needs to know that it is expecting to receive the data. Therefore, a high degree of handshake processing is necessary for the two components (INMOD and utility) to know what is expected.

As well as passing the data, a status code is sent back and forth between the utility and the INMOD. As with all processing, we hope for a successful completion. Earlier in this book, it was shown that a zero status code indicates a successful completion. That same situation is true for communications between the utility and the INMOD.

Therefore, a memory area must be allocated that is shared between the INMOD and the utility. The area contains the following elements:

1. The return or status code
2. The length of the data that follows
3. The data area

The following charts show the various programming statements to define the data elements, status codes and other considerations for the various programming languages.

Parameter definition for FastLoad

RRECORD   DSECT
RETCODE   DS    F
RETLENGTH DS    F
RETDATA   DS    CL<data-length>

Struct {
 long retcode;
 long retlength;
 char buffer(<data-length>);

01 PARM-REC.
03 RETCODE    PIC S9(9) COMP.
03 RETLENGTH  PIC 9(9) COMP.
03 RETDATA    PIC X(<data-length>).

DCL  1 PARM-REC,
       10 RETCODE FIXEDBINARY(31,0)
       10 RETLENGTH  FIXEDBINARY(15,0)
       10 RETDATA PIC X(<data-length>)

Return/status codes from FastLoad to the INMOD

Return/status codes for the INMOD to FastLoad

Entry point for FastLoad used in the DEFINE:

Figure 7-6

NCR Corporation provides two examples for writing a FastLoad INMOD. The first is called BLKEXIT.C, which does not contain the checkpoint and restart logic, and the other is BLKEXITR.C that does contain both checkpoint and restart logic.

The following charts show the data statements used to define the two parameter areas for the various languages.

First Parameter definition for MultiLoad, TPump and FastExport to the INMOD

RRECORD    DSECT
RETCODE    DS    F
RETLENGTH  DS    F
RETDATA    DS    CL<data-length>

Struct {
 long retcode;
 long retlength;
 char buffer(<data-length>);

01 PARM-REC.
   03 RETCODE    PIC S9(9) COMP.
   03 RETLENGTH  PIC 9(9) COMP.
   03 RETDATA    PIC X(<data-length>).

DCL   1 PARM-REC,
        10 RETCODE     FIXED BINARY(31,0)
        10 RETLENGTH   FIXED BINARY(15,0)
        10 RETDATA    PIC X(<data-length>)

Second Parameter definition for INMOD to MultiLoad, TPump and FastExport

IPARM      DSECT
ISEQNUM     DS    F
ILENGTH     DS    H
IDATA       DS    CL<data-length>

Struct {
 long iseqnum;
 short ilength;
 char ibuffer(<data-length>);

01 PARM-REC.
   03 ISEQNUM    PIC 9(9) COMP.
   03 ILENGTH    PIC 9(9) COMP.
   03 IDATA      PIC X(<data-length>).

DCL   1 PARM-REC,
        10 ISEQNUM  FIXED BINARY(31,0)
        10 ILENGTH  FIXED BINARY(15,0)
        10 IDATA    PIC X(<data-length>)

Return/status codes for MultiLoad, TPump and FastExport to the INMOD

The following diagram shows how to use the return codes of 6 and 7:

Return/status codes for the INMOD to MultiLoad, TPump and FastExport:

Entry point for MultiLoad, TPump and FastExport:

As seen above many of the return codes are the same. However, it should also be noted that FastLoad must remember the record count in case a restart is needed, whereas, the other utilities send the record count to the INMOD. If the INMOD fails to accept the record count when sent to it, the job will abort or hang and never finish successfully.

This means that if a FastLoad INMOD is used in one of the other utilities, it will work as long as the utility never requests that a checkpoint take place. Remember that unlike FastLoad, the newer utilities default to a checkpoint every 15 minutes. The only way to turn it off is to set the CHECKPOINT option of the .BEGIN to a number than is higher than the number of records that is being processed.

Therefore, it is not the best practice to simply use a FastLoad INMOD as if it is interchangeable. It is better to modify the INMOD logic for the restart and checkpoint processing necessary to receive the record count and use it for the repositioning operation.

As seen earlier in this book, there is a NOTIFY statement. If the standard values are acceptable, you should use them. However, if they are not, you may write your own NOTIFY routine.

If you chose to do this, refer to the NCR Utilities manual for guidance for writing this processing. We just want you to know here that it is something you can do.

Below is and example of the PROCEDURE DIVISION commands that might be used for MultiLoad, TPump or FastExport.

PROCEDURE DIVISION USING PARM-1, PARM-2.
BEGIN.
MAIN.
{ specific user processing goes here, followed by: }

IF RETCODE= 0 THEN
DISPLAY "INMOD RECEIVED - RETURN CODE 0 - INITIALIZE & READ "
PERFORM 100-OPEN-FILES
PERFORM 200-READ-INPUT
GOBACK
ELSE
IF RETCODE= 1 THEN
DISPLAY "INMOD RECEIVED - RETURN CODE 1- READ"
PERFORM 200-READ-INPUT
GOBACK
ELSE
IF RETCODE= 2 THEN
DISPLAY "INMOD RECEIVED - RETURN CODE 2 - RESTART "
PERFORM 900-GET-REC-COUNT PERFORM 950-FAST-FORWARD-INPUT
GOBACK
ELSE
IF RETCODE= 3 THEN
DISPLAY "INMOD RECEIVED - RETURN CODE 3 - CHECKPOINT "
PERFORM 600-SAVE-REC-COUNT
GOBACK
ELSE
IF RETCODE= 5 THEN
DISPLAY "INMOD RECEIVED - RETURN CODE 5 - DONE "
MOVE 0 TO RETLENGTH
MOVE 0 TO RETCODE
GOBACK
ELSE
DISPLAY "INMOD RECEIVED – INVALID RETURN CODE "
MOVE 0 TO RETLENGTH
MOVE 16 TO RETCODE
GOBACK.
100-OPEN-FILES.
OPEN INPUT DATA-FILE.
MOVE 0 TO RETCODE.
200-READ-INPUT.
READ INMOD-DATA-FILE INTO DATA-AREA1
AT END GO TO END-DATA.
ADD 1 TO NUMIN.
MOVE 80 TO RETLENGTH.
MOVE 0 TO RETCODE.
ADD 1 TO NUMOUT.
END-DATA.
CLOSE DATA-FILE.
DISPLAY "NUMBER OF INPUT RECORDS = " NUMIN.
DISPLAY "NUMBER OF OUTPUT RECORDS = " NUMOUT.
MOVE 0 TO RETLENGTH.
MOVE 0 TO RETCODE.
GOBACK.

Nexus allows you to Pivot your answers sets in a variety of ways. Many manufacturing companies utilize Nexus and they need to be able to pivot data and then graph and chart the data as well. Nexus has very sophisticated pivoting techniques that allow you to see your data just like you demand.

Just right click on any answer set and choose Pivot. The Pivot Wizard will allow you to Pivot your data in any direction. There is a Basic Pivot tool (see below) and an Advanced Tab on the Wizard that will give you the flexibility for your more complicated Pivots.

Download a FREE Trial of the Nexus Query Chameleon at:
www.CoffingDW.com

See the Nexus Query Chameleon User Guide at:
http://www.coffingdw.com/data/Nexus_Product_Info.pdf

Press the HISTORY at the top of Nexus and your most recent SQL queries are right in front of you in full screen easy-to-read mode. Double click on any SQL and you are ready to paste it and go. Advanced search capabilities allow you to find anything you have ever run. You can even decide how many queries you want to see in your history view or show it all.

The Nexus allows you to schedule when you want to run any query, batch job, Load Utility, Compress or Synchronization job. Why even come to work?

Teradata has the ability to use their Multi-Column Compression capabilities, but don’t provide a tool or make it an easy process to compress eligible columns. The Nexus has SmartCompress which compresses tables and saves about 35% space savings. That can literally save a company millions. Is there any bad news? No!

Compressing every table will save space, make queries faster, help with joins, and reduces spool. If you are not compressing your Teradata system you are leaving easy money on the table (well, I should say In the Table).

SmartCompress is extremely flexible and easy-to-use. It provides the ability to check your work before you compress and even provides detailed reports, graphs, and charts showing you your space savings per column.

Nothing is nicer than showing your boss what a great job you are doing. SmartCompress has a REPORT button on it that will allow you to show a wide variety of reports, charts, and graphs for each table compressed. You can even run a fake compress and check the report and then decide to proceed or omit columns.

At the top of Nexus is the Dashboard. Click you shall receive. You can see all types of system metrics in beautiful reports, charts and graphs with fantastic search capabilities.

At the top of Nexus is the Dashboard. You can click on that and choose the Profiler. This will allow you to examine all your columns and see all kinds of information including how well the column distributes and its skew factor.

Profiling data demographics will help enormously in making sure your Primary Index distributes well. This is also a great feature for queries against tables that are running out of spool.