The SQL Data Access clause indicates whether a procedure can issue SQL statements and, if so, what type. An external procedure that contains “SQL statements” actually contains CLIv2, ODBC, or JDBC calls for those statements, not the standard SQL statements themselves.
You can specify the SQL DATA ACCESS and LANGUAGE clauses in any order.
- You can write external procedures to perform SQL requests using CLIv2 or JDBC in the same way a client application can make CLIv2 calls to perform SQL requests.
- You can use the CREATE PROCEDURE (External Form) and REPLACE PROCEDURE (External Form) statements to specify a data access clause that defines the specific capabilities of SQL calls made from the external procedure.
When a CLIv2 external procedure specifies the MODIFIES SQL DATA option for its data access clause, for example, the system knows that the procedure must be linked with an API, but it does not know which API the procedure must be linked with. To handle this, the EXTERNAL NAME clause must explicitly specify that the procedure is to link with the CLIv2 libraries.
Explicitly specifying a package to link with in the EXTERNAL NAME clause is not valid for external procedures that use JDBC. For those procedures, the determination is made based on the combination of the LANGUAGE and SQL Data Access clauses you specify for the CREATE PROCEDURE or REPLACE PROCEDURE request.
Once it knows this information, the system takes care of finding the proper libraries, linking with them, and setting up the proper environmental variables during execution.
To avoid link and execution conflicts with existing UDF libraries, the database maintains a dynamic library that is separate from the standard UDF library for each database that contains CLIv2- or Java-based external procedures.