Working with Data

The utility is able to run scripts that enable data to be inserted into the database. These scripts enable SQL queries to be mixed with instructions to insert data files (IXF, ASCII, etc.) into the database.

There are three different scenarios:

• Insertion of initial data into a newly-created database.

• Insertion of new data into a database that is being updated.

• Import/export of data into/from a database.

Data insertion scripts contain:

• comment lines beginning with ; or #,

• SQL queries (any line beginning with an alphabetical character are be treated as a query),

• escape sequences used to trigger specific actions (e.g.: import a file into a table).

By default, the data scripts consider that the queries are provided line by line.

Formatted scripts can be used by specifying the query separation method via an "--options" comment inserted as the first line of the file. In this mode, the blanks at the beginning and end of the query are deleted.

There are two possible syntaxes:

• Use of a separator character (usually the ; character).
  In this case, the query may be placed on several lines and all the queries end with the specified character. In this case, the comment must indicate the character to use in the "sep_cha" parameter. This syntax does not generally make it possible to execute blocks of PL/SQL-type SQL instructions.
  Example:

  --options sep_char=;
  insert test values (
     1,
     'key one',
     'value key one'
  );
  insert test values (
     2,
     'key two',
     'value key two'
  );

• Use of a command to separate two queries (e.g. "go" if we use syntax compatible with the SQL Server query interpreter or "/" if we use Oracle-compatible syntax).
  In this case, the comment must indicate the character to use in the "sep_command" parameter. This syntax is used to execute a PL/SQL-type code in a "readable way.
  Example:

  --options sep_command=go
  insert test values (
     1,
     'key one',
     'value key one'
  )
  go
  insert test values (
     2,
     'key two',
     'value key two'
  )
  go

  Restriction: the command must be provided on a single line, it is not a real SQL interpreter.

Recognized escape sequences:

\INSERT <TableName> <FileName> <DataType>

\REPLACE <TableName> <FileName> <DataType>

\UPDATE <TableName> <FileName> <DataType>

where INSERT inserts the additional data into the table, REPLACE overwrites the table's contents beforehand and UPDATE updates or inserts data based on the table's primary key.

Note that UPDATE mode is compatible with SQL data sources only. It does not work with BTrieve files.

Example:
\INSERT SYSADEL.HSLREFER HSLREFER.IXF IXF

\RegDWORD Path,Key,Value [action]

Used to make the execution of an instruction conditional, by testing a (numerical) DWORD type value in the registry.

Example:
\RegDWORD HKEY_LOCAL_MACHINE,SOFTWARE\Sample,RegTest,2 INSERT INTO SAMPLE.SAMPLE_DATA VALUES ('r;Reg test value 2 succeeded')

\RegSZ Path,Key,Value [action]

As for \RegDWORD, except that the value tested is a string.

\Commit

\Rollback

Used to insert transaction control instructions into the script source.

Important: We do not recommend using Commit and Rollback instructions directly.

\Autocommit [on|off]

Switches the connection to transactional mode (\Autocommit off) or Autocommit mode (\Autocommit on).

Bear in mind that scripts run in Autocommit mode cannot be re-entered in the event of an error (any instructions preceding the error will not be canceled).

\Dbm [DB2|Oracle|SQL Server...) [action]

Makes execution of an instruction conditional upon the result of a DBMS test.

Note: The Oracle7 and Oracle8 values are supported for script forwards compatibility.

\SetUser <user> <password>

Lets you change the connected user. This instruction validates the current transaction and restores the connection with the specified user.

\System [command]

Executes the specified system command.

For example, to change the LOGFILSIZ parameter in the database (using the substitution sequence {dbt database}):
\system db2cmd /c /w /i db2 update db cfg for {dbt database}using logfilsz 2048

Note: Escape sequences are now also supported in generic PDM scripts.

Substitution escape sequences:

These sequences are used to configure a query according to the DBMS parameters.

Escape sequence substitution can be disabled by inserting the line "Substitution=0" in the description file's [Database] section.

They take the general form {dbt <parameter>}. The following parameters can be queried:

For example, when the command:

\system db2cmd /c /w /i db2 update db cfg for {dbt database}using logfilsz 2048

is executed when the SAMPLE database is created, it will be replaced with:

\system db2cmd /c /w /i db2 update db cfg for SAMPLE using logfilsz 2048

During an update procedure, the system attempts to run the data insertion scripts corresponding to the table structure update scripts. The system first searches for these scripts in the section specific to the DBMS being used, then, if they are not found, in the [Data/Generic] section.

↑ Top of page