The CONNX extended functionality is used in conjunction with the CREATE TABLE primary SQL command to name the (optional) FDT file name used to create the table.

This function is optinal. It can be used with Adabas only.

If no FDT file name is supplied using adabasfdtfname, CONNX creates an FDT file in memory using the SQL data types that have been appropriately transformed.

Example: {adabasfdtfname 'C:\Program Files\Software AG\Adabas\V331\Example\FDT\emp.fdt'}

The CONNX extended functionality is used in conjunction with the CREATE TABLE primary SQL command to name the (optional) target file name on the Adabas server.

This function is optinal. It can be used with Adabas only.

The maximum file name length allowed is 16 characters.

Example: {adabasfilename 'inv.dat'}

If adabasfilename is not used, CONNX attempts to formulate a file name that can be used.

The bsearchtempkey function causes the method chosen for joining data to be a binary search of an ordered list. This is the fastest method for small to medium sets of data. This method is the default for joins that do not have an index, or where the index use is discarded with forcetempkey.

Example: SELECT a.*, b.* FROM db.dbo.tab a, db.dbo.tab b WHERE a.col_name = b.col_name {bsearchtempkey}

Currently, bsearchtempkey is the default method used to process joins, and so the above query would be equivalent to:

This function enables automatic bulk mode processing for standard insert statement. This enables the functionality at the statement level. See AUTOBULKINSERT for more information about this functionality.

This function disables automatic bulk mode processing for standard insert statement. This does not override the global configuration setting AUTOBULKINSERT. This function disables the functionality at the statement level.

This option overrides the default string comparison behavior of the core SQL engine. By default, the core SQL engine makes case insensitive comparisons. This default behavior can be changed globally with a configuration setting. Additionally the behavior can be changed at a statement level with this escape clause. This setting, nor the global setting will change the case comparison behavior of the target database. If an index is used by the database, the databases comparison normal behavior will occur. This setting only applies to string comparisons done within the CONNX core, outside of the database. CONNX metadata system tables are always case insensitive regardless of these settings. When used with a query that invloves a single table, this function will have no affect on the case-sensitivity of the results. Once this is used it remains in effect as long as the process that is being used to run the query is running. Breaking the connection and then reconnecting does not terminate their effect.

Example: SELECT a.*, b.* FROM db.dbo.tab a, db.dbo.tab b WHERE a.unique_key = b.unique_key {caseinsensitive}

This option overrides the default string comparison behavior of the core SQL engine. By default, the core SQL engine makes case insensitive comparisons. This default behavior can be changed globally with a configuration setting. Additionally the behavior can be changed at a statement level with this escape clause. This setting, nor the global setting will change the case comparison behavior of the target database. If an index is used by the database, the databases comparison normal behavior will occur. This setting only applies to string comparisons done within the CONNX core, outside of the database. CONNX metadata system tables are always case insensitive regardless of these settings. When used with a query that invloves a single table, this function will have no affect on the case-sensitivity of the results. Once this is used it remains in effect as long as the process that is being used to run the query is running. Breaking the connection and then reconnecting does not terminate their effect.

Example: SELECT a.*, b.* FROM db.dbo.tab a, db.dbo.tab b WHERE a.unique_key = b.unique_key {casesensitive}

By executing this extended SQL statement, CONNX dynamically enables server side tracing for the currently active database connections.

Example: select 1 {fn enableservertrace}

By executing this extended SQL statement, CONNX dynamically disables server side tracing for the currently active database connections.

Example: select 1 {fn disableservertrace}

By executing this extended SQL statement, CONNX closes all open tables and flushes any open data caches. Table name is an optional parameter. If omitted all tables in the cache are flushed.

Examples: SELECT * FROM customers {fn flushopenfilecache} - to flush all entries in the cache.

SELECT * FROM customers {fn flushopenfilecache 'customers_rms'} - to flush an individual table.

By executing this extended SQL statement, CONNX retrieves a new instance of the CDD file before executing the query.

Example: SELECT * FROM customers {fn refreshcdd}

Updates the index and cardinality information for all table for all connected databases in the data dictionary. Linked databases from linked CDDs will not be updated.

Example: {fn updatestatistics}

By executing the above extended SQL statement, the physical file name can be remapped dynamically. This requires that the record structure of the new file be identical to that of the previous file. Works with any file-based data source, for example, VSAM, C-ISAM, Microfocus, POWERflex, DataFlex, and RMS. When using {fn setfilename} multiple times in a query for the same table name such as the union example below, a table alias must be used.

Example: SELECT * FROM customer {fn setfilename customer, 'c:\customers\customer.dat'}

Example:

Reverts the filename that you have set with the fn setfilename function to the previous file name.

Example:

In the following example, the “fn setfilename” function sets the filename of a file in the “i2vms.dbo.Customers_RMS” table to “'CNXDIR:ORDER.DAT'”. To revert the name of the file to the previous file name, run “fn unsetfilename” as follows:

To disable keyed searches on super descriptors that contain NC or NU constituent fields when criteria is not supplied for all columns of the super descriptor.

{forceadanonukey} only applies to NU or NC compound keys (super descriptors). For more information on how and when to use {forceadanonukey}, see Adabas Performance Tuning.

Example: Select * from localhost.dbo.adabas_file_15 where AA='1234' {forceadanonukey}

To enable keyed searches on super descriptors that contain NC or NU constituent fields when criteria is not supplied for all columns of the super descriptor.

{forceadanukey} only applies to NU or NC compound keys (super descriptors). For more information on how and when to use {forceadanukey}, see Adabas Performance Tuning.

Example: Select * from localhost.dbo.adabas_file_15 where AA='1234' {forceadanukey}

The forcetempkey function is used to abandon the database keys in a lookup and force the creation of a temporary key. This technique is valuable when the connection to the database is slow, and is enhanced when an equal join on an unfiltered column is requested. Under such circumstances, the joins are extremely fast. You do not have to specify the forcetempkey escape for equal and outer joins on columns without indexes. A temporary key join is performed automatically.

Example: SELECT a.*, b.* FROM db.dbo.tab a, db.dbo.tab b WHERE a.unique_key = b.unique_key {forcetempkey} - force the use of a temporary key (binary key is the default).

The hashtempkey function causes the method chosen for joining data to use hashing. This is the fastest method for joining large sets of data.

The following example, which assumes there is no index on the column col_name, uses binary search of an ordered table to reduce work in performing the join.

Example: SELECT a.*, b.* FROM db.dbo.tab a, db.dbo.tab b WHERE a.col_name = b.col_name {hashtempkey}

The ignoretimestampfraction function is useful for comparison of timestamp data across different database systems. If one database system does not support nanoseconds in the timestamp (Oracle is one example of such a database), then we can compare all of the other fields for equality by using this example.

Example: SELECT a.*, b.* FROM db.dbo.tab a, db.dbo.tab b WHERE a.unique_key = b.unique_key {ignoretimestampfraction} - do not check fractional seconds.

Stops a currently running CONNX query from within the current process. The statement IDs of all running queries within the current process can be viewed with the {showsessions} command.

Example: {killstatement 27B663C8}

This keyword limits the number of rows that are returned. An optional offset can be specified as a second parameter. The offset is one-based.

Example: SELECT * FROM customers {maxrows 10} - returns a maximum of 10 rows of data.

SELECT * FROM customers {maxrows 10,4} - return- a maximum of 10 rows of data, starting at the 4th row of output.

This keyword enables identically structured Adabas databases to be aggregated and treated as a single logical database.

Example: {multidbfilter adabas_host 4,5}

This keyword passes a SQL statement to a backend database in its native format.

Example: SELECT * FROM customers {nativesql}

This keyword returns the CONNX native type, length, precision, and scale in the description field of the metadata functions that describe the columns. This metadata is available from the following sources:

The format of the description field is as follows: NativeType = ### :NativeSize = ##### : NativePrecision = ##### : NativeScale ###

Example: NativeType = 1 :NativeSize = 20 : NativePrecision = 0 : NativeScale 0

This keyword switches off {maxrows #} and reestablishes unlimited row count return.

Example: SELECT * FROM customers {nomaxrows}

By executing this extended SQL statement, CONNX disallows passthrough of a SQL statement to SQL Server.

Example: SELECT * FROM customers {nopassthrough}

To use the DBKEY value for subsequent queries in Rdb databases, use the above defined CONNX SQL extended function "{nopassthrough}" on all queries that reference the DBKEY, or you may receive or transmit the passthrough type DBKEY (which is binary in format). In general, it is easier to manipulate DBKEY values in character mode.

To use the Oid value for subsequent queries in PostgreSQL databases, use the above defined CONNX SQL extended function "{nopassthrough}" on all queries that reference the Oid, or you may receive or transmit the passthrough type Oid (which is binary in format). In general, it is easier to manipulate Oid values in character mode.

This keyword turns off the CONNX Advanced SQL Optimization for the duration of this single query. This keyword can be appended to any existing SQL statement that CONNX supports.

Example: SELECT * FROM customers {nosqloptimize}

The {nooptimizeoperator} escape can be used to tell CONNX not to use the prior operator in query optimization. CONNX continues to evaluate the expression, but it is done on the client, rather than potentially using an index, and the associated criteria. For example, using the CONNX sample CUSTOMER table, the following query will be optimized to use the index on CUSTOMERID.

To tell CONNX not to use the index (to perform a table scan), use the following SQL statement:

The {nooptimizeperator} is very useful if you have multiple criterion that have usable indexes, and you want to force a particular index to be used. If there was an index both on the customername and the customerid field, you can force the customerid index to be used as follows:

Conversely if there was an index both on the customername and the customerid field, you can force the customername index to be used as follows:

This keyword tells CONNX not to create a temp key, and to use the Cartesian product instead. Without the use of the {notempkey} escape clause, CONNX creates a temporary key to perform the join. With the {notempkey} escape clause, CONNX forms a brute force nested loop to perform the join.

Example: SELECT * FROM customers a, customers b WHERE a.customername = b.customername and a.customeraddress = b.customeraddress {notempkey}

By executing this extended SQL statement, CONNX enables passthrough of a SQL statement to a database.

Example: SELECT * FROM customers {passthrough <database name>}

The recordmismatch function is used to locate differences between two tables in an equal join. The joining columns must contain all of the columns of a unique index. When the recordmismatch function isused, records only appear in the query where mismatches occur. Therefore, if a database has ten thousand records and exactly two records are different, then the query returns two records in the results set.

Example: SELECT a.*, b.* FROM rms_db.dbo.tab a, oracle_db.dbo.tab b WHERE a.unique_key = b.unique_key {recordmismatch} - check for differences between the records.

By executing this extended SQL statement in the InfoNaut querying tool, session management data for Adabas and the Adabas SQL Gateway (CONNX for Adabas) can be viewed.

Example: {serverlist}

To view results in InfoNaut, preface the command with "Select 1:" select 1{serverlist}

At runtime, you can provide a password for any Adabas file using the following extended CONNX syntax:

For example, to specify a password for the CUSTOMERS_ADABAS Table, issue the following SQL statement: SELECT * FROM adabas_windows.dbo.CUSTOMERS_ADABAS {fn setadapassword CUSTOMERS_ADABAS, PASSWORD}

If you have defined a global password in the data dictionary, the password specified with the SQL statement overrides the global password.

This keyword will return a list of active CONNX connections & statements within the current process. This is useful for diagnostics of applications and servers that manage multiple CONNX connections, such as the JDBC server, or a web server. Individual statements returns from {showsessions} can be killed with the {killstatement} command.

Example: select 1 {showsessions}

By adding the expression noted above to the end of an SQL statement, CONNX will return the query execution plan instead of the requested data.

Example: SELECT * FROM customers, orders WHERE customers.name = "Name" and customers {statistics}

This keyword enables connection pooling from the current point forward if connection pooling is disabled in the CONNX Registry file (see CONNX Registry File Settings). All future disconnects are pooled. This command has no effect if connection pooling is already enabled.

Example: {startconnectionpooling}

This keyword provides a hint CONNX to start with a specific table in a query plan. The hint may not be honored, depending on available query plans.

Example: select * from customers_rms c, orders_rms {startwiththistable} o, products_rms p where p.productid = o.productid and c.customerid = o.customerid

CONNX determines an execution plan based on the various tables/fields that are used. To view the execution plan for a query, the extended CONNX function {statistics} can be placed after the query. The results that are returned are the query plan.

In this example, two indexes are used. CONNX determines that ORDERS_RMS is to be used as the starting table because of the use of an index in the query (ordered=1). In most cases, CONNX selects the most efficient plan. However, the user can force the choice of the starting table by using the extended function {startwiththistable}.

This keyword turns off connection pooling as established in the CONNX Registry file (see CONNX Registry File Settings) and disconnects any pooled connections. All current connections remain open.

Example: {stopconnectionpooling}

{stopconnectionpooling} should be used to shut down connection pooling if an application calls the Windows API "TerminateProcess," which causes unpredictable behavior in the CONNX connection pooling shutdown process.

Execute the above extended SQL statement to commit the transaction and change the transaction mode to read-only. Use for Rdb only.

Execute the extended SQL statement to commit the transaction and change the transaction mode to read/write.

When there are multiple choices for keys within a table, CONNX selects the optimal key choice. There are two ways to manually override the selected key.

The sample PRODUCT table that comes with CONNX has three keys on it:

When issuing the following SQL statement, CONNX will by default select key #0 as the best key candidate.