Background documentationSET Command Locate this document in the navigation structure

 

With this Loader command, you can define many properties of a Loader session and some properties of a database session. You can adjust the predefined values in the control parameters for Loader or database to suit your requirements.

Note Note

The new values are only valid for the current Loader or database session and must be restored if they are needed in subsequent sessions. You can modify the values with a new SET command or, with relevant Loader commands.

End of the note.

Structure

Syntax Syntax

  1. <set_command> ::=
  SET
| SET <bool_spec>
| SET <code_page_spec>
| SET <isolation_level_spec>
| SET <null_spec>
| SET <number_spec>
| SET BACKUPTOOL <backup_tool_spec>
| SET CODETYPE <standard_code_spec>
| SET CONFIGURATION <exclude_include>
| SET CSV '/<separator>/<delimiter>'
| SET CURRENT_SCHEMA = <schema_name>
| SET DATE <standard_date_mask>
| SET FORMAT <standard_date_mask>
| SET LOGFILEPATH '<loader_log_file_path>' [DELETELOGFILE]
| SET MAXERRORCOUNT <unsigned_integer>
| SET TIME <standard_time_mask>
| SET TIMESTAMP <standard_timestamp_mask>
| SET TRANSACTION SIZE <unsigend_integer>

<bool_spec> ::=
  BOOLEAN '<true_value>/<false_value>'

<code_page_spec> ::=
  CODEPAGE [<literal>]

<isolation_level_spec> ::=
  ISOLATION LEVEL <unsigned_integer>

<number_spec> ::=
  DECIMAL '/[<digit_grouping_symbol>]/<decimal_symbol>/'

<backup_tool_spec> ::=
  BACKINT
| NETWORKER
| TSM

<exclude_include> ::=
  EXCLUDE
| INCLUDE

<separator> ::=
  <character>

<delimiter> ::=
  <character>
End of the code.
SET

You can use the SET command without additional specifications. In this case the following log files are displayed:

  • Name and complete path of the Loader log file

  • Complete path to the directory in which Loader saves the packages that were created in transport mode

SET <bool_spec>

You can use syntax rule <bool_spec> (<bool_spec> ::= '<true_value>/<false_value>') to define the character string used in data streams to represent BOOLEAN values that are either exported from the database or imported into the database. true_value defines the character string for values that are true (default value: TRUE), false_value defines the character string for values that are false (default value: FALSE). The character strings can be up to 10 characters in length.

This setting applies to the entire Loader session, provided that the character string is not changed by another SET BOOLEAN command. You can change the current value for individual Loader commands by using the syntax element bool_spec to define the data format (field_format_spec).

SET <code_page_spec>

If you want to use a particular code page for converting CHAR data from ASCII to UCS2, you can specify the code page using the Loader SET command SET <code_page_spec> (<code_page_spec> ::= CODEPAGE [<literal>]). If you do not set a code page, default code page ISO-8859-1 is assumed.

Example Example

SET CODEPAGE "8859–14"- The code page 8859-14 is set.

SET CODEPAGE WINDOS-1252 - The code page WINDOWS-1252 is set.

SET CODEPAGE - The default code page of the Loader ISO-8859-1 is set.

End of the example.
Case 1: The code page for the conversion is a user-defined code page and is not yet contained in the system table CODEPAGE.

To make user-defined code pages accessible to Loader for conversions, you first have to load these code pages into the system table CODEPAGE. To do this, proceed as follows:

  1. In the user-specific configuration directory of Loader (Microsoft Windows: <drive>:\Documents and Settings\<user_id>\Application Data\sdb\loader\config, UNIX: $HOME/sdb/loader/config) create a file <literal>.txt. This file has to be formatted in the same way as the files that are provided by the Unicode consortium at ftp://ftp.unicode.org/Public/MAPPINGS/.

    Example Example

    ...

    # Format:  Three ab-separated columns

    #    Column #1 is the ISO/IEC 8859-8 code (in hex as 0xXX)

    #    Column #2 is the Unicode (in hex as 0xXXXX)

    #    Column #3 the Unicode name (follows a comment sign, '#')

    ...

    0x00 0x0000   #  NULL

    0x01 0x0001   #  START OF HEADING

    0x02 0x0002   #  START OF TEXT

    0x03 0x0003   #  END OF TEXT

    0x04 0x0004   #  END OF TRANSMISSION

    0x05 0x0005   #  ENQUIRY

    0x06 0x0006   #  ACKNOWLEDGE

    0x07 0x0007   #  BELL

    0x08 0x0008   #  BACKSPACE

    0x09 0x0009   #  HORIZONTAL TABULATION

    0x0A 0x000A   #  LINE FEED

    End of the example.

  2. Log on to the database as database system administrator.

  3. Load the code page into the system table CODEPAGE using the SET command. When processing the Loader command SET CODEPAGE <literal>, Loader first tries to find the specified code page in the system table CODEPAGE. If the table does not contain the code page, Loader tries to find the corresponding file <literal>.txt. If this file exists, it is read, and the conversion table is entered in the system table CODEPAGE. If the file is not found, and if the code page is not contained in the system table CODEPAGE, Loader stops processing the SET commands and displays an error message.

If the SET command is processed successfully, the user-defined code page is available in the system table CODEPAGE.

Case 2: The code page for the conversion is already contained in the system table CODEPAGE.

Loader reads the required conversion table from the system table CODEPAGE. All subsequent conversions of the CHAR data from ASCII to UCS2 are performed using this conversion table.

SET <isolation_level_spec>

You can use the syntax rule SET <isolation_level_spec> (<isolation_level_spec> ::= ISOLATION LEVEL <unsigend_integer>) to define the value of the isolation level (unsigned_integer: 0, 1, 10, 15, 2, 20, 3 ,30, more information: SQL Reference Manual, CONNECT Statement (connect_statement)). If you do not set an isolation level, isolation level 3 is assumed. This setting applies to the entire Loader session, provided that the isolation level is not changed by another SET ISOLATION LEVEL command. You can use the USE USER command to set the isolation level for a user and override the value set by the SET command.

SET <number_spec>

You can use the syntax rule <number_spec> (<number_spec> ::= DECIMAL '/[<digit_grouping_symbol>]/<decimal_symbol>/') to specify which characters are used in decimal numbers to group thousands (digit_grouping_symbol: no character or character for structuring thousands, character cannot be a number, default value: no character) and as decimal separator (decimal_symbol: character for separating decimal places, character cannot be a number, default value: period).

If no character is specified, Loader uses the appropriate default value. This setting applies to the entire Loader session, provided that the character string is not changed by another SET DECIMAL command.

Example Example

SET DECIMAL '/ /,/'

Specifies the following number format: 1 999 987,98

SET DECIMAL '///'

Specifies the following number format (Loader default): 1999987.98

End of the example.

You can change the current value for individual commands by using the syntax element number_spec to define the data format (field_format_spec).

SET BACKUPTOOL <backup_tool_spec>

You can use syntax rule SET BACKUPTOOL <backup_tool_spec> (<backup_tool_spec> ::= BACKINT | NETWORKER | TSM) to specify which third-party backup tool is to be used for exporting and importing using third-party backup tools.

  • BACKINT: third-party backup tool supporting Backint for SAP MaxDB and Backint for Oracle

  • NETWORKER: NetWorker (Legato) backup tool

  • TSM: TSM (IBM/Tivoli) backup tool

SET CONFIGURATION <exclude_include>

You can use syntax rule SET CONFIGURATION <exclude_include> (<exclude_include> ::= EXCLUDE | INCLUDE) to exclude tables from processing or include tables in processing.

  • EXCLUDE: negative list, the tables listed in the configuration file are not to be processed

  • INCLUDE: positive list, the tables listed in the configuration file are to be processed

SET CSV '<separator>/<delimiter>'

You can use syntax rule SET CSV '<separator>/<delimiter>/' (<separator> ::= <character>, <delimiter> ::= <character>) to define the characters to be used in CSV format as separator and delimiter.

  • separator: character used to separate data fields from one another (separator representation), default value: comma (,)

    You have to specify exactly one character from the 7–bit ASCII character set (only 1 byte long).

  • delimiter: character used to delimit data in selections (delimiter display), default value: double quotations marks (")

    You can specify exactly one character that originates from the 7 bit ASCII character set (only 1 byte long) or no character (no delimiter).

This setting applies to the entire Loader session, provided that the separator and delimiter are not changed by another SET CSV command. You can change the current value for individual commands by using the syntax element separator_spec or delimiter_spec to define the data format (csv_format_spec).

SET CURRENT SCHEMA = <schema_name>

You can use syntax rule SET CURRENT SCHEMA = <schema_name> to specify the schema schema_name as the current schema (default value: schema that is assigned to the database user who is logged on, more information: SQL Reference Manual, Schema name (schema_name)).

This setting applies to the entire Loader session, provided that the character string is not changed by another SET CURRENT SCHEMA command. You can use the USE USER command or USE SCHEMA command to set the isolation level for a user and override the value set by the SET CURRENT SCHEMA command.

SET FORMAT <standard_date_mask>

You can use syntax rule SET FORMAT <standard_date_mask> to define in which format Loader transfers date and time values to the database in SQL statements. When processing Loader commands, Loader generally transfers date and time values in INTERNAL format. If the date and time values embedded in SQL statements are to be transferred in another format, these have to be explicitly set with this command. This setting applies to the entire Loader session, provided that the format is not changed by another SET FORMAT command.

Example Example

INSERT INTO <table_name> values ('2008–01–01', 1)

As the date is to be transferred in ISO format here, the format must be set using SET FORMAT ISO.

End of the example.
SET LOGFILEPATH '<loader_log_file_path>' [DELETELOGFILE]

The Loader log file loader_<timestamp>.log or loader.log is located in the default log file directory (Loader Log File). You can use the syntax rule SET LOGFILEPATH <loader_log_file_path> to set the log file path for the Loader log file. You can use the syntax rule SET LOGFILEPATH <loader_log_file_path> DELETELOGFILE to set the log file path for the Loader log file and delete the Loader log file already located in <loader_log_file_path>.

This setting applies to the entire Loader session, provided that the path is not changed by another SET LOGFILEPATH command.

SET MAXERRORCOUNT <unsigned_integer>

You can use syntax rule SET MAXERRORCOUNT <unsigend_integer> to define how many errors Loader should accept when a specific command is executed before it cancels processing of the command. This setting applies to the entire Loader session, provided that the error count is not changed by another SET MAXERRORCOUNT command.

SET TRANSACTION SIZE <unsigned_integer>

You can use syntax rule SET TRANSACTION SIZE <unsigend_integer> to define the number of data records after which a COMMIT is carried out.

More Information

Opening a Session: Loader Commands