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>
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.
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.
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.
Specifies the name of an ACL table, or format file (.fmt), with a table layout that you want to use.
Specifies the schema to connect to. You must enclose the schema name in quotation marks.
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.
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.
Specifies the database tables that you want to access. The database_table value must be a quoted string.
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"
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"
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.
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.
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"