IIR will create various files while processing some jobs. The PATH parameters can be used to specify where those files will be placed. IIR will use the path specified in the

DEFAULT-PATH

parameter. If

DEFAULT-PATH

has not been specified, the current directory will be used. It is valid to specify a value of "+" for the path.

This represents the value associated with the environment variable

SSAWORKDIR

. This is especially recommended when running an API program and/or system

remotely

, that is from a directory other than the

SSAWORKDIR

directory.

This is used to specify System wide defaults.

COMPRESS-METHOD(n)

the compression method to use when storing

Compressed-Key-Data

. Method 0 (the default) will store records longer than 255 bytes as multiple adjacent IDX records. This improves performance as it takes advantage of the locality of reference in the DBMS’ cache. Method 1 will truncate the IDX records if their size exceeds 255 bytes and fetch them from the IDT instead. This causes additional I/O when truncated IDX records are referenced.

VPD-SECURE

marks the System as secure. IIR will insist that all search users provide VPD context information prior to starting a search. Refer to the

Virtual Private Databases

section in this guide for details.

This parameter is used to customize the behavior of IIR when loading tables and indexes.

These options give fine control over table size, placement and sorting and are particularly important when loading large amounts of data. The syntax is:

Type (Name, Database_Type, Text1 [, Text2]), ...

where

Name is the name of the

IDT

or

IDX

. A value of

"default"

may be specified to define defaults for all IDTs and IDXs. A specific name will override the default for a particular Type.

Database_Type]

is either

ORA

for Oracle,

UDB

(for IBM UDB/DB2), or

MSQ

for Microsoft SQL Server. This allows the specification of options for all target database types, permitting the same SDF to be used for multiple target databases.

Text1

and

Text2

a list of values whose format and content is

Type

dependent. All types require at least

Text1

to be provided, and some types require

Text2

.

IIR creates database tables and indexes using

CREATE TABLE

and

CREATE INDEX SQL

statements.

This

Type

is used to specify where those tables and indexes will be placed and/or what attributes they will have.

This gives the local Database Administrator control over the extent sizes and location (Tablespace) of IIR tables and indexes.

Text1

is used to specify options for Table creation, while

Text2

is used for Index creation options. They must start with

table=

and

index=

respectively.

The strings that follow these tokens are DBMS specific text which is appended to the

CREATE TABLE

and

CREATE INDEX SQL

statements. Text is appended immediately after the specification of the columns in the table (for

CREATE TABLE

) and immediately after the list of columns in the index (for

CREATE INDEX

). The text must be syntactically correct for the target DBMS, otherwise a run-time error will result when the table or index is created.

Oracle: The IDX is implemented as an Index Organized Table (IOT). The storage for an IOT is defined using the

table=

parameter. The

index=

parameter is ignored in this case.

Example 1:

DATABASE-OPTIONS=
IDT (default, ora, "table=storage(initial 10M next 1M)",
"index=storage(initial 10M next 1M)"),
IDX (default, ora, "table=storage(initial 10M next 1M)",
"index=storage(initial 10M next 1M)"

This example defines default extent sizes for both IDTs and IDXs stored on an Oracle database.

UDB: In this example, tables and indexes created for the IDT called

idt-39

are placed in

TABLESPACE USERSPACE1

and indexes on those tables have a

PCTFREE

value of zero. All IDX indexes have a

PCTFREE

of zero.

DATABASE-OPTIONS=
IDT (idt-39, udb, "table=IN USERSPACE1 INDEX IN USERSPACE1", "index=PCTFREE 0",
IDX (default, udb, "", "index=PCTFREE 0")

This Type is used to specify the path for the file created by the DBMS load utility that contains rejected records.

Oracle: The default path is the current work directory.

UDB: The default is

/tmp

on the server machine. When using a

UDB LOAD

utility, this path is relative to the machine where the DBMS server is running.

DATABASE-OPTIONS=
           IDTBAD (IDT55, udb, "/tmp/rejects/"),

This Type is used to specify the Code Page of the data to be loaded. It is only used by MS SQL. Oracle and UDB ignore it. The parameter is passed to the bcp mass load utility with the

-C

switch. The Code Page effects the translation of

CHAR

and

VARCHAR

columns to the Server’s code page and should only be specified if the SQL Server’s code page is not the same as the client’s code page.

The "client" in this situation is the machine where the IIR Table Loader runs on (which is normally the machine where the Console Server is running).

This

Type

is used to set the number of data errors that will be tolerated while loading the IDT.

Text1

specifies the maximum number of data errors that are allowed. The default is zero. The Table Loader will terminate abnormally if more than

Text1

data errors are encountered. Rejected records are written to a file with an extension of

.err

in the

IDTBAD

directory.

DATABASE-OPTIONS=
         IDTERR (IDT96, ora, "100"),

This allows up to 100 data errors when loading IDT96. If more than 100 errors occur, the Table Load will terminate with an error.

This

Type

is used to set the field delimiter character(s). This character is used to delimit fields in the IDT loader file. The default value is 0x01. The specified value must be in the range 1 to 255 and no field may contain the delimiter character.

DATABASE-OPTIONS=
          IDTFD (IDT96, ora, "255")

If it is not possible to select a field delimiter character that is not contained in any field value, use a fixed-format IDT load file (Loader-Definition,

OPTIONS=Fixed

).

Oracle: Two delimiter characters may be defined by specifying a value in the range 1 to 65535. The first delimiter character is defined as

value%256

and the second delimiter is

value/256

. A delimiter character value of 0x00 is not permitted. For example,

DATABASE-OPTIONS= IDTFD (IDT96, ora, "258")

defines the first and second delimiter as 0x02 and 0x01 respectively.

This Type is used to specify the name and path of the database mass load utility. If this parameter has not been defined, IIR uses the utility specified by the

SSASQLLDR

environment variable. For example:

This

Type

is used to control the placement of temporary loader files. By default, files are created in the

DEFAULT-PATH

. When large amounts of data are to be loaded, it is advantageous to place the files on different disks to avoid I/O contention problems.

Text1

specifies the name of the directory where the file will be placed. For example:

DATABASE-OPTIONS=
        IDTTMP (IDT96, ora, "c:/tmp"),
        IDXTMP (nameidx, ora, "d:/tmp")

This places the data file for

IDT96

in

c:/tmp

and the data file for the IDX named

nameidx

in

d:/tmp

.

where

nnn

is the size of the sort buffer in megabytes (or kilobytes/gigabytes if k/G is specified). The default size is 512 MB. Do not make the memory buffer so large as to cause swapping, as this will negate the benefit of a fast memory based sort.

t

is the number of threads to use for the sort process. It defaults to the number of CPUs on the machine where the Table Loader runs.

path1

is the directory where any temporary sort work-files will be created. Very large loads may require two sort work-files. These should be placed on different disks if possible.

path2

is the directory where the second sort work-file will be created (if necessary).

The sort path used is a hierarchy, depending on which parameters have been specified. The parameters in order of highest to lowest precedence are

IDXSORT

,

SORT-WORK-PATH

and

DEFAULT-PATH

.

For example:

DATABASE-OPTIONS=
         IDXSORT (default, ora, "mem=256M,threads=2"),
         IDXSORT (nameidx, ora, "mem=512M,path1=/dev1,path2=/dev2")

This

Type

is used to specify storage parameters for partitioned UDB databases. This is necessary because each unique index of a table in a partitioned tablespace must contain all distribution columns of the table.

We can specify a partitioning key for the IDT with an

IDT

database option:

DATABASE-OPTIONS=
          IDT(IDT-00, udb, "table=in TESTSPACE partitioning key(EmpNum) using hashing"),

Now each unique index will require the partitioning key as an additional key. This can be implemented with an

IDTPART

, which provides a list of the partitioning keys.

For example:

DATABASE-OPTIONS= IDTPART(IDT-00, udb, "EmpNum")

Use the

SEQUENCE

type to generate record IDs in a database sequence for a single identity table or all identity tables.

Use the following format:

DATABASE-OPTIONS=SEQUENCE(default|<IDT name>, <Database type>, ["start=<N>","increment=<N>"])

The format uses the following attributes:

The following example sets the database sequence for a specific identity table in Oracle database:

DATABASE-OPTIONS= SEQUENCE(IDT38, ora, " ", "increment=3")