DEFINE TABLE DB command

Used to define ACL server tables by connecting to ACL Analytics Exchange Connector.

Syntax

DEFINE TABLE DB {SOURCE database_profile <PASSWORD num> <PASSWORD num> |
SERVER server_profile <PASSWORD num>} <FORMAT format_name>
SCHEMA schema <TITLED acl_table_name> <PRIMARY|SECONDARY>
{DBTABLE db_tablename FIELDS {field_names|ALL}} <...n> 
<WHERE condition> <ORDER field_names> 

Parameters

SOURCE database_profile

Specifies the ACL database profile to use to access the database engine. Database profiles include information required to connect to the database engine, including a reference to the associated server profile, the database type, database name, and user account information.

PASSWORD num

Specifies a password when the database profile does not contain saved passwords. Use PASSWORD twice after the SOURCE keyword. The first password logs you on to the server, and the second one logs you on to the database.

You can set the password if you do not want the script to stop until you enter a password. However, the password appears in your script when you use this method.

SERVER server_profile

No longer used.

Prior to version 10.0 of ACL, this parameter was used when connecting to ACL Server Edition for z/OS. Version 10.0 of ACL, and subsequent versions, no longer include ACL Server Edition for z/OS.

FORMAT format_name

Specifies the name of an ACL table, or format file (.fmt), with a table layout that you want to use.

SCHEMA schema

Specifies the schema to connect to. You must enclose the schema name in quotation marks.

TITLED acl_table_name

Specifies the name of the ACL table to create. The acl_table_name value must be a quoted string. If you omit this parameter, ACL uses the database table name. When you access more than one table at a time, ACL uses the name of the first one.

PRIMARY | SECONDARY

Specifies whether to use the table as a primary or secondary table in multifile commands. If neither value is specified, the default value of PRIMARY is used.

DBTABLE database_table

Specifies the database tables that you want to access. The database_table value must be a quoted string.

FIELDS field_names | ALL

Optional. Specifies the fields that you want to include from specific tables. Enter the names of particular fields or enter ALL to include all fields. The field_names value must be a quoted string.

To use fields from more than one table, specify the table name followed by the fields from that table, then the next table followed by the fields from that table, and so on.

For example:

DBTABLE "DSN1310"
FIELDS "Field_A Field_B Field_C"
DBTABLE "DSN2516"
FIELDS "Field_L Field_M Field_N"
WHERE condition

Limits the data to those records that meet the specified condition. You must use valid SQL syntax to specify the condition, and the condition value must be a quoted string.

When you join more than table, ACL displays the condition of the join in the WHERE clause. For example: "Table_1.First_name = Table_2.First_name"

ORDER field_names

Specifies the fields the records should be sorted on by the database engine. There is a performance cost associated with ordering records, so this parameter should only be used when the sequence is important. The field_names value must be a quoted string.

Remarks

The ACL server table is defined as a query that uses a database profile to connect to a database table.

Using ACL Analytics Exchange Connector, you can access an unlimited number of related tables, but no more than five is recommended. Processing time increases when you access multiple tables.

When using the DEFINE TABLE DB command to define an ACL table you can suppress the time portion of datetime values by prefacing the command with the SET SUPPRESSTIME command. This capability is intended for use in pre-version-10.0 ACL scripts that assume the time portion of datetime values will be truncated. If SET SUPPRESSTIME ON is not added to these scripts, they will not run in the datetime-enabled version of ACL. For more information, see the “SET SUPPRESSTIME” section in SET command.

Example

The following example uses the DEFINE TABLE DB command to access data from a Microsoft SQL Server database via ACL Analytics Exchange Connector. The SOURCE parameter is used to connect to ACL Analytics Exchange Connector through a database profile.

DEFINE TABLE DB SOURCE "SQLServer_Audit" SCHEMA "HR" TITLED "Payroll"
DBTABLE "HR.Employee" FIELDS "EmployeeID"
DBTABLE "HR.EmployeePayHistory" FIELDS "Rate PayFrequency"
WHERE "HR.Employee.EmployeeID=HR.EmployeePayHistory.EmployeeID"


(C) 2013 ACL Services Ltd. All Rights Reserved. | Send feedback