SET Command 

With this Loader command you can define many properties of a Loader session. You can adjust predefined values of the control parameters of the Loader to suit your own requirements.

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

Syntax

<set_commando>            ::= 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 MAXERRORCOUNT <unigned_integer>
                            | SET TIME <standard_time_mask>
                            | SET TIMESTAMP <standard_timestamp_mask>
                            | SET TRANSACTION SIZE <unsigned_integer>

<bool_spec>               ::= BOOLEAN '<true_value>/<false_value>'
<code_page_spec>          ::= CODEPAGE [<literal>]
<isolation_level_spec>    ::= ISOLATION LEVEL <unsigned_integer>
<null_spec>               ::= NULL value representation
<number_spec>             ::= DECIMAL '/[<digit_grouping_symbol>]/<decimal_symbol>/'

<backup_tool_spec>        ::= BACKINT | NETWORKER | TSM
<standard_code_spec>      ::= code specification
<exclude_include>         ::= EXCLUDE | INCLUDE
<separator>               ::= <character>
<delimiter>               ::= <character>
<schema_name>             ::= <identifier>
<standard_date_mask>      ::= date specifications
<standard_time_mask>      ::= time specifications
<standard_timestamp_mask> ::= time stamp specifications

SET command without additional specification	Display the following log files: - Name and complete path of the Loader log - Complete path to the directory in which the Loader saves the packages that were created in transport mode
BOOLEAN '<true_value>/<false_value>'	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 may have a maximum length of 10 characters. See the SQL Reference Manual, Character String
CODEPAGE [<literal>]	literal: name of the code page, default value: ISO-8859-1 If you specify an empty value, the code page is reset to the default value. See the SQL Reference Manual, Literal (literal)
ISOLATION LEVEL <unsigned_integer>	unsigned_integer: value of the isolation level to be set, default value: 3 see also: SQL Reference Manual, CONNECT Statement (connect_statement), Unsigned_Integer (unsigned_integer)
DECIMAL '/[<digit_grouping_symbol>] /<decimal_symbol>/'	digit_grouping_symbol: defines a character or no character for structuring thousands Default value: no character decimal_symbol: defines the character for separating the decimal places Default value: period The character cannot be a number. See the SQL Reference Manual, Character (character)
BACKUPTOOL <backup_tool_spec> <backup_tool_spec> ::= BACKINT | NETWORKER | TSM	You can specify which backup tool from other providers is to be used for Exporting and Importing with Backup Tools from Other Providers. BACKINT: backup tool from other providers supporting Backint for MaxDB and Backint for Oracle NETWORKER: NetWorker (Legato) backup tool TSM: TSM (IBM/Tivoli) backup tool
CONFIGURATION <exclude_include> <exclude_include> ::= EXCLUDE | INCLUDE	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.
CSV '/<separator>/<delimiter>/' <separator> ::= <character> <delimiter> ::= <character>	separator: character that separates data fields are separated from each other if the CSV format is used (separator representation), default value: comma (,) You have to specify exactly one character that originates from the 7 bit ASCII character set (only 1 byte long). delimiter: character used to delimit data in selections when the CSV format is used (delimiter dispay), 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). See the SQL Reference Manual, Character (character)
CURRENT_SCHEMA = <schema_name> <schema_name> ::= <identifier>	The schema_name schema is specified as the current schema Default value: Schema that is assigned to the database user who is logged on See the SQL Reference Manual, Schema Name (schema_name), Identifier (identifier)
MAXERRORCOUNT <unigned_integer>	unsigned_integer: number of errors that Loader accepts See the SQL Reference Manual, Unsigned Integer (unsigned_integer)
TRANSACTION SIZE <unsigned_integer>	unsigned_integer: number of data records after which a COMMIT is carried out.

Explanation

Some of the options of the SET command are explained in more detail below.

SET <bool_spec>

You use the syntax rule SET <bool_spec> to define the character string that is used to represent in data streams BOOLEAN values that are either exported from the database instance or imported into a database instance. 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 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 page using the SET command SET <code_page_spec>.

SET CODEPAGE "8859-14"
SET CODEPAGE WINDOS-1252

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 the 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 the Loader (Microsoft Windows: <drive>:\Documents and Settings\<user_id>\Application Data\sdb\loader\config, UNIX: $HOME/sdb/loader/config) create a file called <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/.

...
# 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

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

       3.      Load the code page into the system table CODEPAGE.
To do this, use the SET command. When processing the SET command SET CODEPAGE <literal>, the Loader first tries to find the specified code page in the system table CODEPAGE. If the table does not contain the code page, the SAP DB 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, the 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.

The 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 use the syntax rule SET ISOLATION LEVEL <unsigned_integer> to define the value of the isolation level (permitted values: 0, 1, 10, 15, 2, 20, 3, 30). If you do not set an isolation level, isolation level 3 is assumed implicitly. 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 use the syntax rule SET <number_spec> to specify which characters are to be used in decimal numbers to group thousands and as decimal separator. 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.

SET DECIMAL '/ /,/'
Specifies the following number format: 1 999 987,98

SET DECIMAL '///'
Specifies the following number format (Loader default): 1999987.98

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 CSV '/<separator>/<delimiter>/'

You use the syntax rule SET CSV '/<separator>/<delimiter>/' to define the characters that are to be used in the CSV format as separator and delimiter. This setting applies to the entire Loader session provided that the separator and delimiter is 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>

The specified schema is defined as the current schema. 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 command.

SET FORMAT <standard_date_mask>

You use this command to specify in which format the Loader transfers date and time values in SQL statements to the database. During the processing of Loader commands, the 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.

INSERT INTO <table_name> values ('2005-01-01', 1)

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

SET MAXERRORCOUNT <unsigned_integer>

You use the syntax rule SET MAXERRORCOUNT <unsigned_integer> to define how many errors the 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.

See also:

Creating a Database Session: Commands