Data Warehouse Center Administration Guide

Appendix D. Defining values for Visual Warehouse 5.2 compatible programs

This appendix provides information for users of Visual Warehouse 5.2 who want to run certain Visual Warehouse 5.2 programs in the Data Warehouse Center.

Defining values for a Visual Warehouse 5.2 DB2 UDB Data Export (VWPEXPT1) program

Use this step to run the Visual Warehouse DB2 UDB Data Export (VWPEXPT1) warehouse program in the Data Warehouse Center. You use the Data Export program to export data from the local DB2 database.

Connect a warehouse source to this step in the Process Model window before you define the values for this step subtype. Parameter values for this step subtype will be defined automatically based on your source definition.

If the export process generates warnings, the program returns as successfully completed.

This program is available for the following operating systems:

The Column Mapping page is not available for this step.

To define values for a step that uses this program:

  1. Open the step notebook.
  2. Specify general information about the program. For more information, see Providing general information about a step.
  3. On the Parameters page, double-click the Parameter value field for the SELECT statement and specify a SELECT statement for the program.
  4. Optional: Double-click the Parameter value field for the File type modifier string parameter, and type the file type modifier string. For example, the File type modifier string parameter "coldel," indicates that columns must be separated by commas. Do not insert a space between coldel and the comma. Make sure that you enclose this parameter in double quotation marks. Otherwise, the command line processor interprets some characters as file redirection characters.
  5. On the Processing Options page, provide information about how your step processes. For more information, see Defining processing options.
  6. Click OK to save your changes and close the step notebook.

Defining values for a Visual Warehouse DB2 UDB Data Load Insert (VWPLOADI) program

Use this step to run the Visual Warehouse 5.2 DB2 UDB Data Load Insert (VWPLOADI) warehouse program in the Data Warehouse Center. You use VWPLOADI to load data from a flat file into a DB2 table, appending to existing data.

Connect the step to warehouse source and a warehouse target in the Process Model window.

The Visual Warehouse 5.2 DB2 UDB Load Insert warehouse program extracts the following step and warehouse source parameter values from the Process Model window and your step definition:

These parameters are predefined. You do not specify values for these parameters. Additionally, the step passes other parameters for which you provide values. Before the program loads new data into the table, it exports the table to a backup file, which you can use for recovery.

Recommendation: Create the target table in its own private DB2 tablespace. Any private tablespace that you create will be used by default for all new tables that do not specify a tablespace. If processing fails, DB2 might put the whole tablespace in hold status, making the tablespace inaccessible. To avoid this hold problem, create a second private tablespace for steps that do not use the load programs.

To create a tablespace: 

CREATE TABLESPACE tablespace-name MANAGED BY SYSTEM USING ('d:/directory')

where directory is the directory that is to contain the databases. DB2 creates the directory for you.

Restrictions: The Data Warehouse Center definition for the warehouse agent site that is running the program must include a user ID and password. The DB2 load utility cannot be run by a user named SYSTEM. Be sure to select the same warehouse agent site in the warehouse source and the warehouse target for the step using the program. The database server does not need to be on the agent site. However, the source file must be on the database server. Specify the fully qualified name of the source files as defined on the DB2 server.

If the warehouse program detects a failure during processing, the table will be emptied. If the load generates warnings, the program returns as successfully completed.

The warehouse program does not collect database statistics. Run the DB2 UDB RUNSTATS program after a sizable load is complete.

This warehouse program is available for the following operating systems:

The Column Mapping page is not available for this step.

To define a values for a step that uses this warehouse program:

  1. Open the step notebook.
  2. Specify general information about the warehouse program. For more information, see Providing general information about a step.
  3. Double-click the Parameter value field for the Backup file name parameter, and type the fully qualified name of the backup file.
  4. Double-click the Parameter value field for the File type modifier string parameter, and type the file type modifier string. For example, the File type modifier string parameter "coldel," indicates that columns must be separated by commas. Do not insert a space between coldel and the comma. Make sure that you enclose this parameter in double quotation marks. Otherwise, the command line processor interprets some characters as file redirection characters.

    The file type modifier can use the following modifiers:
    Modifier Description
    Chardel x x is a single-character string delimiter. The default value is a double quotation mark ("). The character that you specify is used in place of double quotation marks to enclose a character string. You can specify a single quotation mark (‘) as a character string delimiter as follows: Modified by chardel ‘’
    Coldel x x is a single-character column delimiter. The default value is a comma (,). The character that you specify is used instead of a comma to signal the end of a column. Do not insert a space between coldel and the comma. Enclose this parameter in double quotation marks. Otherwise, the command line processor interprets some characters as file redirection characters. In the following example, coldel ; causes the export utility to interpret any semicolon (;) it encounters as a column delimiter: Db2 "export to temp of del modified by coldel; select * from staff where dept = 20"
    Dateisiso Dataisiso causes all date data values to be exported in ISO format.
    Decplusblank Decplusblank causes positive decimal values to be prefixed with a blank space instead of a plus sign (=). The default action is to prefix positive decimal values with a plus sign.
    Decpt x x is a single-character substitute for the period as a decimal point character. The default value is a period (.). The character that you specify is used in place of a period as a decimal point character.

    See the DB2 Utilities book for more information about these modifiers.

  5. On the Processing Options page, provide information about how your step processes. For more information, see Defining processing options.
  6. Click OK to save your changes and close the step notebook.

Defining values for a Visual Warehouse DB2 UDB Data Load Replace (VWPLOADR) program

Use this step to run the Visual Warehouse 5.2 DB2 Data Load Replace (VWPLOADR) warehouse program in the Data Warehouse Center. You use VWPLOADR to load data from a flat file into a DB2 table, replacing existing data.

Connect the step to warehouse source and a warehouse target in the Process Model window.

The Visual Warehouse 5.2 DB2 UDB Load Replace warehouse program extracts the following step and warehouse source parameter values from the Process Model window and your step definition:

These parameters are predefined. You do not specify values for these parameters.

Recommendation: Create the target table in its own private DB2 tablespace. Any private tablespace that you create will be used for all new tables that do not specify a tablespace. If processing fails, DB2 might put the whole tablespace in hold status, making the tablespace inaccessible. To avoid this hold problem, create a second private tablespace for steps that do not use the load programs.

To create a tablespace: 

CREATE TABLESPACE tablespace-name MANAGED BY SYSTEM USING ('d:/directory')

where directory is the directory that is to contain the databases. DB2 creates the directory.

Restrictions: The Data Warehouse Center definition for the agent site that is running the program must include a user ID and password. The DB2 load utility cannot be run by a user named SYSTEM. Be sure to select the same agent site in the warehouse source and the warehouse target for the step that uses the warehouse program. The database server does not need to be on the agent site. However, the source file must be on the database server. Specify the fully qualified name of the source files as defined on the DB2 server.

If the warehouse program detects a failure during processing, the table is emptied. If the load generates warnings, the program returns as successfully completed.

The DB2 UDB Load Replace program collects database statistics during the load, so you do not need to run the DB2 UDB RUNSTATS (VWPSTATS) program after this program.

This warehouse program is available for the following operating systems:

To define a values for a step that uses this program:

  1. Open the step notebook.
  2. Specify general information about the warehouse program. For more information, see Providing general information about a step.
  3. Optional: On the Parameters page, double-click the Parameter value field for the File type modifier string parameter, and type the file type modifier string. For example, the File type modifier string parameter "coldel," indicates that columns must be separated by commas. Do not insert a space between coldel and the comma. Make sure that you enclose this parameter in double quotation marks. Otherwise, the command line interprets some characters as file redirection characters.

    The file type modifier can use the following modifiers:
    Modifier Description
    Chardel x x is a single-character string delimiter. The default value is a double quotation mark (“). The character that you specify is used in place of double quotation marks to enclose a character string. You can specify a single quotation mark (‘) as a character string delimiter as follows: Modified by chardel ‘’
    Coldel x x is a single-character column delimiter. The default value is a comma (,). The character that you specify is used instead of a comma to signal the end of a column. Do not insert a space between coldel and the comma. Enclose this parameter in double quotation marks. Otherwise, the command line processor interprets some characters as file redirection characters. In the following example, coldel ; causes the export utility to interpret any semicolon (;) it encounters as a column delimiter: Db2 “export to temp of del modified by coldel; select * from staff where dept = 20”
    Dateisiso Dataisiso causes all date data values to be exported in ISO format.
    Decplusblank Decplusblank causes positive decimal values to be prefixed with a blank space instead of a plus sign (=). The default action is to prefix positive decimal values with a plus sign.
    Decpt x x is a single-character substitute for the period as a decimal point character. The default value is a period (.). The character that you specify is used in place of a period as a decimal point character.

    See the DB2 Utilities book for more information about these modifiers.

  4. On the Processing Options page, provide information about how your step processes. For more information, see Defining processing options.
  5. Click OK to save your changes and close the step notebook.

Defining values for a Visual Warehouse 5.2 DB2 UDB REORG (VWPREORG) program

Use this step to run the Visual Warehouse 5.2 DB2 UDB REORG (VWPREORG) program in the Data Warehouse Center. This step runs the DB2 UDB REORG utility on a target table.

You schedule this step to run on the target table of a process, after the process completes. In the Process Model window, draw a data link from the step to the warehouse target.

The Visual Warehouse 5.2 DB2 UDB REORG program extracts the following step and warehouse source parameter values from the Process Model window and your step definition:

These parameters are predefined. You do not specify values for these parameters.

To define a values for a step that uses this program:

  1. Open the step notebook.
  2. Specify general information about the warehouse program. For more information, see Providing general information about a step.
  3. On the Processing Options page, provide information about how your step processes. For more information, see Defining processing options.
  4. Click OK to save your changes and close the step notebook.

Define values for a Visual Warehouse 5.2 DB2 UDB RUNSTATS (VWPSTATS) program

Use this step to run the Visual Warehouse 5.2 DB2 UDB RUNSTATS (VWPSTATS) warehouse program in the Data Warehouse Center.

This step runs the DB2 UDB RUNSTATS utility on a target table. You schedule this step to run on the target table of a process, after the process completes. In the Process Model window, draw a data link from the step to the warehouse target.

The Visual Warehouse 5.2 DB2 UDB RUNSTATS warehouse program extracts the following step and warehouse source parameter values from the Process Model window and your step definition:

These parameters are predefined. You do not specify values for these parameters.

To define a values for a step that uses this warehouse program:

  1. Open the step notebook.
  2. Specify general information about the warehouse program. For more information, see Providing general information about a step.
  3. On the Processing Options page, provide information about how your step processes. For more information, see Defining processing options.
  4. Click OK to save your changes and close the step notebook.

Defining values for a Visual Warehouse 5.2 Load flat file into DB2 UDB EEE (VWPLDPR) program (AIX only)

Use this step to run the Visual Warehouse 5.2 Load flat file into DB2 UDB EEE (AIX only) (VWPLDPR) program in the Data Warehouse Center. This step loads data from a delimited flat file into a DB2 Universal Database Extended Enterprise Edition database, replacing existing data.

Before you use this warehouse program, you must be familiar with parallel system concepts and parallel load.

The VWPLDPR program performs the following steps when it is loading data to a parallel database:

  1. Connects to the target database.
  2. Acquires the target partitioning map for the database.
  3. Splits the input file so each file can be loaded on a node.
  4. Runs remote load on all nodes.

If the load step fails on any node, the VWPLDPR program does the following:

  1. Builds an empty load data file for each node.
  2. Loads the empty data files.

The VWPLDPR program extracts the following step and warehouse source parameter values from the Process Model window and your step definition:

These parameters are predefined. You do not specify values for these parameters. Additionally, there are a number of parameters that you must provide values for.

The Load flat file into DB2 UDB EEE program does not run the DB2 RUNSTATS utility after the load. If you want to automatically run the RUNSTATS utility after the load, add a step to your process that runs RUNSTATS .

Recommendation: Create the target table in its own private DB2 tablespace. Any private tablespace that you create will be used for all new tables that do not specify a tablespace. If processing fails, DB2 might put the whole tablespace in hold status, making the tablespace inaccessible. To avoid this hold problem, create a second private tablespace for steps that do not use the load programs.

To create a tablespace: 

CREATE TABLESPACE tablespace-name MANAGED BY SYSTEM USING (d:/directory')

where directory is the directory that is to contain the databases. DB2 creates the directory.

This program runs on AIX.

To define a values for a step that uses this warehouse program:

  1. Open the step notebook.
  2. Specify general information about the program. For more information, see Providing general information about a step.
  3. On the Parameters page, provide values for the following parameters:
    1. Double-click the Parameter value field for the Column delimiter parameter, and type the column delimiter. The most common values are a comma (,) or a semicolon (;).
    2. Double-click the Parameter value field for the string parameter, and type the string parameter. The most common value is a double quotation mark ("), entered as "\"".
    3. Double-click the Parameter value field for the Decimal delimiter parameter, and type the decimal delimiter. The most common values are a period (.) or a comma (,).
    4. Double-click the Parameter value field for the local, non-shared file directory parameter, and type the local, non-shared file directory.
    5. Double-click the Parameter value field for the Path name and prefix for the parameter, and type the path name and prefix for the split files. The name of each file will consist of the prefix plus a numeric identifier.
    6. Double-click the Parameter value field for the Partition key parameter, and type a parameter for each partition key. The partition key must be in the format used by the db2split database utility. The format generally is as follows: col1,1,,,N,integer followed by col3,3,,5N,character
  4. On the Processing Options page, provide information about how your step processes. For more information, see Defining processing options.
  5. Click OK to save your changes and close the step notebook.

[ Top of Page | Previous Page | Next Page ]