SQL Anywhere Panorama Beta Program Information
Welcome to the SQL Anywhere Panorama Beta Program! This RSS feed is used to provide you with the latest information about SQL Anywhere Panorama, as well as relaying announcement regarding the beta program.
If you have not done so already, please be sure to familiarize yourself with the beta program web site. You will find the software in the Downloads section. Please don't forget to submit your feedback and bug reports to the private beta newsgroup.
Thank you for being a SQL Anywhere Panorama Beta Participant!
Testing and debugging
Testing and debugging distributed SQL programs is similar to testing and debugging local SQL programs, but certain aspects of the process are different.
If applications are coded so that the relational database names can easily be changed by recompiling the program, by changing the input parameters to the program, or by making minor modifications to the program source, most testing can be accomplished using a single system.
After the program has been tested against local data, the program is then made available for final testing on the distributed relational database network. Consider testing the application locally on the system that will be the application server (AS) when the application is tested over a remote connection, so that only the program needs to be moved when the testing moves into a distributed environment.
Debugging a distributed SQL program uses the same techniques as debugging a local SQL program. You use the Start Debug (STRDBG) command to start the debugger and to put the application in debug mode. You can add breakpoints, trace statements, and display the contents of variables.
However, to debug a distributed SQL program, you must specify the value of *YES for the UPDPROD parameter. This is because i5/OS® distributed relational database support uses files in library QSYS and QSYS is a production library. This allows data in production libraries to be changed on the application requester (AR). Issuing the Start Debug (STRDBG) command on the AR only puts the AR job into debug mode, so your ability to manipulate data on the AS is not changed.
While in debug mode on the AR, informational messages are entered in the job log for each SQL statement run. These messages give information about the result of each SQL statement. A list of SQL return codes and a list of error messages for distributed relational database are provided in the Troubleshooting topic.
Informational messages about how the system maximizes processing efficiency of SQL statements are also issued as a result of being in debug mode. Because any maximization occurs at the AS, these types of messages do not appear in the AR job log. To get this information, the AS job must be put in debug mode.
A relatively easy way to start debug mode on the system, if you are using TCP/IP, is to use the QRWOPTIONS data area. However, you cannot specify a specific program to debug with this facility. For details on setup, see QRWOPTIONS data area usage. The data area can be used not only to start debug, but to start job traces, request job logs, display job output and do other things as well. You can even do the QRWOPTIONS setup on an i5/OS AR, and have the options shadowed to a System i™ platform.
If both the AR and AS are System i products, and they are connected with APPC, you can use the Submit Remote Command (SBMRMTCMD) command to start the debug mode in an AS job. Create a DDM file as described in the Setting up DDM files topic. The communications information in the DDM file must match the information in the relational database directory entry for the relational database being accessed. Then issue this command:
SBMRMTCMD CMD('STRDBG UPDPROD(*YES)') DDMFILE(ddmfile name)
The (SBMRMTCMD) command starts the AS job if it does not already exist and starts the debug mode in that job. Use one of the methods for monitoring relational database activity to examine the AS job log to find the job.
The following method for putting the AS job into debug mode works with any AR and a DB2 Universal Database™ for iSeries™ AS with certain restrictions. It depends on being able to pause after the application makes a connection to do setup. It also assumes that what you want to trace or otherwise debug occurs after the connection is established.
Sign on to the AS and find the AS job.
Issue the Start Service Job (STRSRVJOB) command from the interactive job (the job you are using to find the AS job) as shown:
STRSRVJOB (job-number/user-ID/job-name)
The job name for the (STRSRVJOB) command is the name of the AS job. Issuing this command lets you issue certain commands from your interactive job that affect the AS job. One of these commands is the Start Debug (STRDBG) command.
Issue the (STRDBG) command using a value of *YES for the UPDPROD parameter in the interactive job. This puts the AS job into debug mode to produce debug messages on the AS job log.
To end this debug session, either end your interactive job by signing off or use the End Debug (ENDDBG) command followed by the End Service Job (ENDSRVJOB) command.
Because the AS job must be put into debug before the SQL statements are run, the application might need to be changed to allow you time to set up debug on the AS. The AS job starts as a result of the application connecting to the AS. Your application can be coded to enter a wait state after connecting to the AS until debug is started on the AS.
If you can anticipate the prestart job that will be used for a TCP/IP connection before it occurs, such as when there is only one waiting for work and there is no interference from other clients, you do not need to introduce a delay.
When a program is created, the i5/OS licensed program stores information about all collections, tables, views, SQL packages, and indexes referred to in SQL statements in an SQL program.
Parent topic: Preparing distributed relational database programs
Related reference
End Debug (ENDDBG) command
End Service Job (ENDSRVJOB) command
Start Debug (STRDBG) command
Start Service Job (STRSRVJOB) command
SQL CALL statement (stored procedures)
Submit Remote Command (SBMRMTCMD) command
Working with SQL packages
An SQL package is an SQL object used specifically by distributed relational database applications. It contains control structures for each SQL statement that accesses data on an application server (AS).
An SQL package is an SQL object used specifically by distributed relational database applications. It contains control structures for each SQL statement that accesses data on an application server (AS).
These control structures are used by the AS at run time when the application program requests data using the SQL statement.
You must use a control language (CL) command to create an SQL package because there is no SQL statement for SQL package creation. You can create an SQL package in two ways:
Using the CRTSQLxxx command with a relational database name specified in the RDB parameter.
Using the Create SQL Package (CRTSQLPKG) command
Using the Create SQL Package (CRTSQLPKG) commandYou can enter the Create SQL Package (CRTSQLPKG) command to create an SQL package from a compiled distributed relational database program. You can also use this command to replace an SQL package that was created previously.
After an SQL package is created, you can manage it the same way as you manage other objects on the i5/OS operating system, with some restrictions.
Parent topic: Application development
Parent topic: Application development
Related reference
Distributed relational database statements
SQL package creation
Binding an application
Administration
As an administrator for a distributed relational database, you are responsible for work that is done on several systems.
Work that originates on your local system as an application requester (AR) can be monitored in the same way as any other work is monitored on the i5/OS® operating system.
When you are tracking units of work being done on the local system as an application server (AS), you use the same tools but look for different kinds of information.
This topic discusses ways that you can administer the distributed relational database work being done across a network. Most of the commands, processes, and other resources discussed here do not exist just for distributed relational database use. They are tools provided for any i5/OS operations. All administration commands, processes, and resources discussed here are included with the i5/OS licensed program, along with all of the DB2 Universal Database™ for iSeries™ functions. The i5/OS work management functions provide effective ways to track work on several systems.
As an administrator for a distributed relational database, you are responsible for work that is done on several systems.
Work that originates on your local system as an application requester (AR) can be monitored in the same way as any other work is monitored on the i5/OS® operating system.
When you are tracking units of work being done on the local system as an application server (AS), you use the same tools but look for different kinds of information.
This topic discusses ways that you can administer the distributed relational database work being done across a network. Most of the commands, processes, and other resources discussed here do not exist just for distributed relational database use. They are tools provided for any i5/OS operations. All administration commands, processes, and resources discussed here are included with the i5/OS licensed program, along with all of the DB2 Universal Database™ for iSeries™ functions. The i5/OS work management functions provide effective ways to track work on several systems.
You can use control language (CL) commands, all of which provide similar information, but in different ways, to give you a view of work on the i5/OS operating system.
As an administrator in a distributed relational database, you might have to operate a remote System i™ product.
The term connection in this topic collection refers to the concept of an SQL connection. An SQL connection lasts from the time an explicit or implicit SQL CONNECT is done until the logical SQL connection is terminated by such means as an SQL DISCONNECT, or a RELEASE followed by a COMMIT.
You can use the Display Program References (DSPPGMREF) command to determine which tables, data areas, and other programs are used by a program or SQL package. This information is only available for SQL packages and compiled programs and can be displayed, printed, or written to a database output file.
Attempting to delete a collection that contains journal receivers might cause an inquiry message to be sent to the QSYSOPR message queue for the application server (AS) job. The AS and application requester (AR) job wait until this inquiry is answered.
The i5/OS job accounting function gathers data so you can determine who is using the system and what system resources they are using.
The DRDA® and DDM TCP/IP server does not typically require any changes to your existing system configuration. At some time, you might want to change the way the system manages the server jobs to better meet your needs, to solve a problem, to improve the system performance, or to look at the jobs on the system.
This topic discusses how to audit program access to the relational database directories.
Parent topic: Distributed database programming
Data availability and protection
In a distributed relational database environment, data availability involves not only protecting data on an individual system in the network, but also ensuring that users have access to the data across the network.
The i5/OS® operating system provides the following array of functions to ensure that data on systems in a distributed relational database network is available for use:
Save/restore
Journal management and access path journaling
Commitment control
Auxiliary storage pools
Checksum protection
Mirrored protection and the uninterruptible power supply
While the system operator for each system is typically responsible for backup and recovery of that system's data, you should also consider aspects of network redundancy as well as data redundancy. When you are planning your strategy, ensure the optimum availability of data across your network. The more critical certain data is to your enterprise, the more ways you should have to access that data.
Failures that can occur on a computer system are a system failure (when the entire system is not operating); a loss of the site because of fire, flood, or similar catastrophe; or the damage or loss of an object. For a distributed relational database, a failure on one system in the network prevents users across the entire network from accessing the relational database on that system.
Network redundancy provides different ways for users on the distributed relational database network to access a relational database on the network.
Data redundancy in a distributed relational database also provides different ways for users on the distributed relational database network to access a database on the network.
SQL programming
DB2 Universal Database™ for iSeries™ provides a wide range of support for Structured Query Language (SQL).
The examples of SQL statements shown in this topic collection are based on the sample tables and assume that the following statements are true:
They are shown in the interactive SQL environment or they are written in ILE C or in COBOL. EXEC SQL and END-EXEC are used to delimit an SQL statement in a COBOL program.
Each SQL example is shown on several lines, with each clause of the statement on a separate line.
SQL keywords are highlighted.
Table names provided in the sample tables use the schema CORPDATA. Table names that are not found in the Sample Tables should use schemas you create.
Calculated columns are enclosed in parentheses, (), and brackets, [].
The SQL naming convention is used.
The APOST and APOSTSQL precompiler options are assumed although they are not the default options in COBOL. Character string literals within SQL and host language statements are delimited by single-quotation marks (').
A sort sequence of *HEX is used, unless otherwise noted.
Whenever the examples vary from these assumptions, it is stated.
Because this topic collection is for the application programmer, most of the examples are shown as if they were written in an application program. However, many examples can be slightly changed and run interactively by using interactive SQL. The syntax of an SQL statement, when using interactive SQL, differs slightly from the format of the same statement when it is embedded in a program.
Note: By using the code examples, you agree to the terms of the Code license and disclaimer information.
This topic highlights the changes made to this topic collection for V5R4.
Use this to view and print a PDF of this information.
Structured Query Language (SQL) is a standardized language for defining and manipulating data in a relational database. These topics describe the System i™ implementation of the SQL using DB2® UDB for iSeries and the DB2 Query Manager and SQL Development Kit licensed program.
Data definition language (DDL) describes the portion of SQL that creates, alters, and deletes database objects. These database objects include schemas, tables, views, sequences, catalogs, indexes, and aliases.
Data manipulation language (DML) describes the portion of SQL that manipulates or controls data.
A sort sequence defines how characters in a character set relate to each other when they are compared or ordered. Normalization allows you to compare strings that contain combining characters.
DB2 UDB for iSeries provides various methods for protecting SQL data from unauthorized users and for ensuring data integrity.
Routines are pieces of code or programs that you can call to perform operations.
Most data types, such as INTEGER and CHARACTER, do not have any special processing characteristics. However, a few data types require special functions or locators to use them effectively.
You can use SQL in many different environments.
A distributed relational database consists of a set of SQL objects that are spread across interconnected computer systems.
Reference information for SQL programming includes sample tables and CL commands.
No comments:
Post a Comment