SQL statements
In AMT-COBOL, SQL statements for databases and views require a slightly different command than the COBOL standard of EXEC SQL.
Due to the number of database systems that AMT supports, with each of them having different SQL implementations, AMT-COBOL uses SQL language specific EXEC blocks.
Depending on the system where the application was migrated from and the database system being used with AMT, the following EXEC blocks are available:
|
EXEC block: |
Description: |
| EXEC DB2 ... END-EXEC. |
For applications migrated from a IBM z/OS mainframe, accepts DB2 syntax SQL statements. |
| EXEC RDMS ... END-EXEC. |
For applications migrated from a Unisys OS2000 mainframe, accepts RDMS syntax SQL statements. |
Which system the application was originally migrated from can been viewed in the Application Options field 'Application Origin'.
Both the DB2 and the RDMS EXEC blocks will be translated by AMT to either MSSQL or ORACLE statements depending on the
database platform used for AMT.
To avoid confusion, EXEC SQL statements are not allowed in AMT-COBOL.
Accessing other AMT application databases
(MSSQL databases only)
To access a database table of another AMT Cobol application in the same repository, the table name needs to be prefixed by the name of the application within braces/curly brackets (e.g. {Application_Name}.Tablename ). The application name should match the name of the other application in the repository to be able to correctly verify the code.
Example:
SELECT FIRSTNAME, LASTNAME
INTO :WS-FIRSTNAME, :WS-LASTNAME
FROM {CUSTOMER_MANAGEMENT}.T_CUSTOMERS
WHERE CUST_NO = :WS-CUST_NO
END-EXEC.
As the application name in the repository can be different from the application name as set in the Control Center, a database alias matching the application name in the repository should be added to the application database in the control center. These aliases will always use same connection, regardless of the "new connection" option.
MSSQL Note:
Whenever possible, AMT uses FAST_FORWARD cursors on MSSQL databases to increase performance on large result
sets.
As an example the following COBOL code snippet and how AMT translates it internally for usage on an MSSQL database:
COBOL
DECLARE TEST_CURSOR CURSOR FOR
SELECT COL1 FROM TEST.
END-EXEC.
Internal SQL statement:
SELECT COL1 FROM TEST.
This only happens when the cursor is read only and read forward. FETCH cursors and FETCH NEXT cursors will also
make use of the FAST_FORWARD cursor.
FAST_FORWARD is not used when:
- FETCH FIRST/PRIOR/LAST was found or
- the cursor has a FOR UPDATE clause or
- UPDATE WHERE CURRENT OF cursor was found or
- DELETE WHERE CURRENT OF cursor was found.