DEFINE TABLE DB command

Defines an Analytics server table by connecting to a database table using AX Connector. You can connect to a Microsoft SQL Server, Oracle, or DB2 database.

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	The Analytics 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 the database name user account information Note DEFINE TABLE DB supports connecting to only the following databases: Microsoft SQL Server, Oracle, or DB2.
PASSWORD num optional	The password definition to use. You do not use PASSWORD num to prompt for, or specify, an actual password. The password definition refers to a password previously supplied or set using the PASSWORD command, the SET PASSWORD command, or the PASSWORD analytic tag. num is the number of the password definition. For example, if two passwords have been previously supplied or set in a script, or when scheduling an analytic, PASSWORD 2 specifies that password #2 is used. For more information about supplying or setting passwords, see: PASSWORD command SET command PASSWORD tag The password is only required if 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.
SERVER server_profile	No longer used. Prior to version 10.0 of Analytics, used when connecting to ACL Server Edition for z/OS. From version 10.0 of Analytics, ACL Server Edition for z/OS is no longer included.
FORMAT format_name optional	The name of an Analytics table, or table layout file (.layout), with a table layout that you want to use.
SCHEMA schema	The schema to connect to. You must enclose the schema name in quotation marks.
TITLED acl_table_name optional	The name of the Analytics table to create. acl_table_name must be a quoted string. If you omit TITLED, Analytics uses the database table name. When you access more than one table at a time, Analytics uses the name of the first one.
PRIMARY \| SECONDARY optional	Use the table as either a primary or secondary table in multi-file commands. If neither option is specified, the default value of PRIMARY is used.
DBTABLE database_table	The database table that you want to access. database_table must be a quoted string.
FIELDS field_names \| ALL	The fields to include in the output: FIELDS field_names use the specified fields field_names must be a quoted string. ALL use all fields in the table To use fields from more than one table: Enter the first table name followed by the fields from that table. Enter the next table name followed by the fields from that table. For each additional table, repeat step b. DBTABLE "DSN1310" FIELDS "Field_A Field_B Field_C" DBTABLE "DSN2516" FIELDS "Field_L Field_M Field_N" Note Using AX 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.
WHERE condition optional	An SQL WHERE clause that limits the data to those records that meet the specified condition. You must use valid SQL syntax entered as a quoted string. When you join tables, Analytics displays the condition of the join in the WHERE clause: "Table_1.First_name = Table_2.First_name"
ORDER field_names optional	The fields the database engine uses to sort records. field_names must be a quoted string. The command takes longer to run when sorting records. Only use ORDER when sorting is important.

Examples

Example

You want to access data from a Microsoft SQL Server database via AX Connector. To do this, you use the DEFINE TABLE DB command. You include the SOURCE parameter to connect to AX 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"

Remarks

How it works

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

Suppressing the time portion of datetime values

Preface the DEFINE TABLE DB command with the SET SUPPRESSTIME command to suppress the time portion of datetime values.

Using SET SUPPRESSTIME ON is for pre-version-10.0 Analytics scripts that assume the time portion of datetime values will be truncated. If SET SUPPRESSTIME ON is not added to these scripts, they cannot run in the datetime-enabled version of Analytics.

For more information, see the "SET SUPPRESSTIME" section in SET command.