Loading a Sybase database

To setup the properties of the stage, double-click the Sybase Enterprise icon. You see fields and properties under the Properties tab by default.

1. Under the Target category:
   • Specify a Write Method of ASE Write or IQ Write.

     ASE Write uses bcp to load data into a table. Bcp can run in fast or slow mode. If any triggers or indexes have been defined on table to write to, bcp automatically runs in slow mode, and you do not have to set any specific database properties. Otherwise, bcp runs in fast mode. However, bcp cannot run in fast mode unless you set the database property Select into/bulkcopy to True. To set this property, run the following commands by logging in as a system administrator by using the iSQL utility.

     use master go
     sp_dboption <database name>, "select into/bulkcopy", true
     go
     use <database name>
     go
     checkpoint
     go

   • Specify the table to which you are writing.
   • Specify the Write Mode. By default, the IBM® InfoSphere® DataStage® appends to existing tables. You can also choose to create a new table, replace an existing table, or keep existing table details but replace all the rows.
2. Under the Connection category, specify connection details for server, user, and password. You can enter these values directly or insert the values by using the job parameter popup list. In case you want to create a new job parameter, you must create a new environment variable for that parameter. You can create parameters at the job level or at the project level. By default, the InfoSphere DataStage connects to the Sybase default database. However, you can specify a different database if required.
3. Under the Options category, specify the following properties:
   • Truncate Column Names: This only appears for the Write Method of IQWrite or ASE Write. You can set the value as True or False. Set True to truncate field names to the size allowed by the Sybase. Set False to disable the truncation of field names.
   • Default String Length: This is an optional property and only appears for the Write Method of IQWrite or ASE Write. It is set to the size of 1 byte by default. This property sets the default string length of variable-length strings written to a table. The maximum length you can set is 2000 bytes. Note that the stage always allocates the specified number of bytes for a variable-length string. In this case, setting a value of 2000 allocates 2000 bytes for every string. Therefore, you should set the expected maximum length of your largest string and no larger. In case of NLS maximum = 2000 / number of bytes per NLS character. For example, in a Unicode database, a Japanese character occupies 3 bytes.
   • Use Identity:This only appears for the Write Method of ASE Write. This option is used to control the identity column values. When this option is set to True, the values for the identity column are generated by the server. When this option is set to False, the values are generated from the incoming data.
   • Open Command: Use it to specify a command, in single quotes, to be parsed and executed by the Sybase database on all processing nodes before the table is opened. You can specify a job parameter if required.
   • Close Command: Use it to specify any command, in single quotes, to be parsed and executed by the Sybase database on all processing nodes after the stage finishes processing the table. You can specify a job parameter if required.
   • Create Statement: This option appears only for a Sybase ASE write operation. The Create Statement property appears only if you select a write mode of Create or Replace, under the Target category. Use this option to create the table that you want to create or replace.
   • Length to Truncate: Enter the length to which to truncate columns names.
   • Drop Unmatched Field: You can set one of two values, either True or False. Set True to silently drop all input columns that do not correspond to columns in an existing table. Otherwise the stage reports an error and terminates the job. The default value is False.
   • Row Commit Interval: Specify the number of records to be committed before the start of a new transaction. This option is considered only if Insert Array Size is set to 1 or Row Commit Interval is set to Insert Array Size.
   • Output Reject Records: This appears only for the Write Method of IQ Upsert, IQ Write, or ASE Write. It is False by default. Set it to True to send rejected records to the reject link. To send bad records down the reject link, you must set the environment variable APT_IMPEXP_ALLOW_ZERO_LENGTH_FIXED_NULL.
   • Max Reject Records: This property appears only for a Sybase ASE write operation. The Max Reject Records property appears if you set the Output Reject Records property to True. You can specify a maximum number of records to be sent to the reject link.