Chapter 4: Static Binding

Overview

SQL Option, by default, uses dynamic SQL. However, depending on your application the use of static SQL can have a significant impact on performance. Most applications will run faster when executing static as opposed to dynamic SQL. Also, default qualifiers can be specified during the bind to allow a program to use a particular AUTHID to qualify one part table names when the user does not have authority to execute a SET CURRENT SQLID command. Many sites are reluctant to use SET CURRENT SQLID since it may require SYSADM authority to execute.

Creating a DBRM

To have your program use static SQL, you must create a DBRM (database request module) and bind it to the SQL Option server you are going to run against. To create a DBRM, you need to compile your program with at least the following SQL Option preprocessor directives:

• DBRM=dbrm-name
• COLLECTION-ID=collection-id

Binding a DBRM

You must bind the DBRM using the BIND utility provided with SQL Option. After you have bound the DBRM your application will be running static SQL. If you change any SQL statements in the program you will need to rebuild and rebind your DBRM. For details on how to BIND the DBRM, see the BIND Guide.

Autobinding

If you have too many programs to bind using the BIND utility or just don't want to have to manually bind each program, you can compile each program with the SQL Option preprocessor directive called AUTOBIND. When this directive is specified the preprocessor will call BIND for you and will automatically bind the DBRM at the end of the compile.

XDB now supports writing out multiple DBRM's for a single program and you can automatically bind up to 10 DBRM's. The directives that support multiple options are DBRM, COLLECTION-ID, AUTHID, and LOCATION. You must also specify AUTOBIND for this to work properly. See the section SQL Option Preprocessor Directives for more details.

Specifying Options with AUTOBIND

The Bind Guide specifies many bind options. New directives have been added so you can set these options using the AUTOBIND directive. These directives are optional - you do not need to specify them if you want to use default values (recommended in most cases). These additional directives are:

• EXIST-CHECK
• GRANT-EXECUTE
• DEFAULT-CHAR
• BLOCKING

See the section SQL Option Preprocessor Directives for details of each directive.

If any errors occur during the automated bind process, no DBRM will be generated. However in its place will be a log file with name dbrm-name.log The log file will record all errors reported during the bind process. Consult your error documentation to resolve the error.

Binding SQL Statements With Literals

If you have literals in your SQL statements, you need to make sure that the quote character matches your build settings or you could have problems binding the DBRM.

The SQL Option preprocessor defaults to the compiler setting for literals in the build settings. If you use the same character to enclose literals in your SQL statements as you did for COBOL literals, you don't have to do anything. If you use a different character for literals in SQL statements, then you have to use the preprocessor directive of either QUOTE or APOST. See the section SQL Option Preprocessor Directives for more details.

Using AUTHID to Change Table Qualifier

If you have two identical sets of tables, for example YYYYYYYY.table1 and XXXXXXXX.table1 and want to test against both sets of tables, you can use the AUTHID preprocessor directive to switch which qualifier is used.

For example, if you set it to:

XDB(AUTHID=XXXXXXXX)

XXXXXXXX becomes the default qualifier. To run against both sets of data all you need to do is change the value of the AUTHID directive to YYYYYYYY and recompile. The next time you run your application you will be pointing to YYYYYYY.table1.

Examples of DBRM Directives

The following are examples of various DBRM directives used together:

XDB(dbrm=mydbrm location=mvsprod grant-execute=public 
    collection-id=nasecoll)
XDB(collection-id=nasecoll authid=naseprod autobind 
    exist-check=required)

Directives can be entered on multiple lines or as one long string. Each string must start with "XDB", unless entered on the IDE/Build Settings/EXEC SQL/Preproc Additional directives entry field.

Examples of Autobinding Multiple DBRM's

The following are examples of how to autobind a program and generate multiple DBRM's:

XDB(dbrm=mydbrm1;mydbrm2 location=mvsprod;mvstest 
    grant-execute=public collection-id=nasecoll;dbnats 
    autobind)

XDB(collection-id=nasecoll authid=naseprod;nasetest 
    autobind dbrm=testdbrm1;testdbrm2 exist-check=required )

When multiple directives are specified, they must be delimited by a semi-colon with no spaces in between. Commas and blanks are option delimiters and may cause the directive string to not be processed correctly. See the section SQL Option Preprocessor Directives for more details.

Copyright © 2007 Micro Focus (IP) Ltd. All rights reserved.