User guide

ManualsBrandsEXP Computer ManualsSwitchThinConnect4

Oracle® JDBC for Rdb

User Guide

June 2008

Release 7.2.5.3

Oracle JDBC for Rdb User Guide, Release 7.2.5.3

The Programs (which include both the software and documentation) contain proprietary

information of Oracle Corporation; they are provided under a license agreement containing

restrictions on use and disclosure and are also protected by copyright, patent, and other

intellectual and industrial property laws. Reverse engineering, disassembly, or decompilation

of the Programs is prohibited.

The information contained in this document is subject to change without notice. If you find

any problems in the documentation, please report them to us in writing. Oracle Corporation

does not warrant that this document is error free. Except as may be expressly permitted in

your license agreement for these Programs, no part of these Programs may be reproduced or

transmitted in any form or by any means, electronic or mechanical, for any purpose, without

the express written permission of Oracle Corporation.

If the Programs are delivered to the U.S. Government or anyone licensing or using the

Programs on behalf of the U.S. Government, the following notice is applicable:

U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation

and technical data delivered to U.S. Government customers are "commercial technical data"

pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental

regulations. As such, use, duplication, disclosure, modification, and adaptation of the

Programs, including documentation and technical data, shall be subject to the licensing

restrictions set forth in the applicable Oracle license agreement, and, to the extent applicable,

the additional rights set forth in FAR 52.227-19, Commercial Computer Software -

Restricted Rights (June, 1987). Oracle Corporation, 500 Oracle Parkway, Redwood City, CA

94065.

The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or

other inherently dangerous applications. It shall be the licensee's responsibility to take all

appropriate fail-safe, back up, redundancy and other measures to ensure the safe use of such

Summary of content (163 pages)

PAGE 1
Oracle® JDBC for Rdb User Guide June 2008 Release 7.2.5.3 Oracle JDBC for Rdb User Guide, Release 7.2.5.3 Copyright © 2005, 2008 Oracle Corporation. All rights reserved. The Programs (which include both the software and documentation) contain proprietary information of Oracle Corporation; they are provided under a license agreement containing restrictions on use and disclosure and are also protected by copyright, patent, and other intellectual and industrial property laws.
PAGE 2
applications if the Programs are used for such purposes, and Oracle Corporation disclaims liability for any damages caused by such use of the Programs. Oracle is a registered trademark of Oracle Corporation. All other company or product names mentioned are used for identification purposes only and may be trademarks of their respective owners. The Programs may provide links to Web sites and access to content, products, and services from third parties.
PAGE 3
Contents Preface .................................................................................................................................................................................7 Purpose of This Manual.....................................................................................................................................................7 Intended Audience..............................................................................................................................
PAGE 4
5.2 SSL and the Controller......................................................................................................... 59 5.3 SSL Configuration Options .................................................................................................. 60 5.4 Using Self-Signed Certificates for Testing........................................................................... 61 Chapter 6 Oracle JDBC for Rdb Controller ........................................................................
PAGE 5
8.7 Ignoring Statement.cancel() Method Calls ........................................................................ 113 8.8 Inactivity timeouts.............................................................................................................. 114 8.8.1 Client connection timeout ......................................................................................... 114 8.8.2 Server Inactivity Timeout .........................................................................................
PAGE 6
10.3 Sample Setup, Starting an Oracle JDBC for Rdb thin server from Oracle SQL/Services.152 10.4 Sample configuration file MY_SERVERS.XML ................................................................ 158 10.5 Datatype Mapping from Oracle Rdb to java.sql.Types .................................................... 160 10.6 Datatype Mapping from java.sql.Types to Oracle Rdb .................................................... 161 10.7 JDBC Specification SQL to Java Datatype Mappings ................
PAGE 7
Preface Purpose of This Manual The Oracle JDBC for Rdb 7.2.5.3 User Guide describes concepts, features and usage of the Oracle JDBC for Rdb drivers and servers. This user guide covers Oracle JDBC for Rdb for OpenVMS on both Alpha and Integrity Servers.
PAGE 8
Hewlett-Packard Company is often referred to as HP. The following conventions are used in this document: word A lowercase word in a format example indicates a syntax element that you supply. [] Brackets enclose optional clauses from which you can choose one or none. {} Braces enclose clauses from which you must choose one alternative. ... A horizontal ellipsis means you can repeat the previous item. . . .
PAGE 9
Chapter 1 Introduction Oracle provides the following Oracle JDBC for Rdb drivers: Oracle JDBC for Rdb native driver for client-side use with an Oracle Rdb installation Oracle JDBC for Rdb thin driver, a 100 percent pure Java driver for client-side use without an Oracle Rdb installation. This is particularly useful with applets. The Oracle JDBC for Rdb drivers provide the same basic functionality. They both support the following standards and features: JDK 1.4 / JDBC 3.
PAGE 10
Management of the Oracle JDBC for Rdb servers may be carried out using the Oracle JDBC for Rdb controller or by using the Oracle SQL/Services manager.
PAGE 11
Chapter 2 Oracle JDBC for Rdb There are two types of Oracle JDBC for Rdb drivers: Oracle JDBC for Rdb native driver Oracle JDBC for Rdb thin driver 2.1 Oracle JDBC for Rdb Native Driver The Oracle JDBC for Rdb native driver is a Type II driver intended for use with clientserver Java applications. The native driver, written in a combination of Java and C, converts JDBC invocations to calls to SQLMOD modules, using native methods to call C-entry points.
PAGE 12
Table 2-1 RdbNative Format Elements Element Description Is the full file specification of the Rdb database that you wish to connect to. These optional switches may be used to specify certain settings that should be established when the database connection is made. See Connection Options for more details. Example To connect to MY_DB_DIR:PERSONNEL: Connection conn = DriverManager.
PAGE 13
The thin driver allows a direct connection to any Oracle Rdb database via an Oracle JDBC for Rdb server using TCP/IP on Java sockets. Note: When the thin driver is used with an applet, the client browser must have the capability to support Java sockets. 2.2.
PAGE 14
Connection conn = DriverManager.getConnection( "jdbc:rdbThin://bravo:1701/my_db_dir:personnel",user, pass); Note: The should be a valid OpenVMS-style file specification or logical name, for example: my_disk:[my_directory]my_database When you use an Oracle Rdb thin driver connection, any logical names and relative directory specifications used in the database specification must be valid for the account and directory from which the Oracle Rdb thin server was started. 2.2.
PAGE 15
Default Description The value 0 means unlimited idle time allowed. See Client connection timeout for more details handshakeTries Decimal or hex integer 500 Sets the maximum number of times the main process will attempt to establish handshake with its associated executor sub-process. This option is only valid on connections using rdbNative driver and when multiprocess is enabled on the native connection. This option may only be used in conjunction with the multiprocess option.
PAGE 16
Default hex integer Description client connection will wait on the completion of a read or write on the network. If the read or write does not complete within the designated time an exception will be raised. The value 0 means unlimited time allowed. This timeout is only applicable to the thin driver and is only placed on the client-side socket operations. sqlcache Decimal or hex integer 0 Specifies the number of statements that may be maintained in the SQL cache.
PAGE 17
Example To connect using the thin driver via an Oracle JDBC for Rdb server to MY_DB_DIR:PERS on node BRAVO using port 1755 and enabling full trace logging for this connection: Connection conn = DriverManager.getConnection( "jdbc:rdbThin://bravo:1755/my_db_dir:pers@tracelevel=-1", user, pass); Alternatively , these options may be placed in a properties block: Properties info = new Properties(); info.put("user", user); info.put("password", password); info.
PAGE 18
Example To set trace level to trace everything for your application that utilizes either the Rdb native or Rdb thin driver: $java –Doracle.rdb.jdbc.
PAGE 19
Chapter 3 Oracle JDBC for Rdb Servers Oracle JDBC for Rdb servers are the server-side components that services JDBC requests issued by applications using the Oracle Rdb thin driver. There are three types of Oracle JDBC for Rdb servers: Oracle JDBC for Rdb thin server Oracle JDBC for Rdb multi-process server Oracle JDBC for Rdb pool server Each server is multi-threaded, able to handle multiple client requests at the same time.
PAGE 20
3.1.1 Starting a Thin Server A thin server may be started by using the appropriate start statement within the controller, as an Oracle SQL/Services JDBC dispatcher or directly from the operating system command line. 3.1.1.1 Starting a Thin Server from the Oracle JDBC for Rdb controller A thin server may be started from the controller by referencing a thin server definition in an XML-formatted configuration file. See Starting Servers within Oracle JDBC for Rdb Controller for more details.
PAGE 21
SQLSRV> 3.1.1.3 Starting a Thin Server from the Command Line You may invoke a thin server from the OpenVMS command line. Instead of placing a number of options on the command line, you may wish to create a server definition within an XML-formatted configuration file and then start the server using its server name. The server type for this server definition must be set to RdbThinSrv for a standard thin server. Format $ java –jar rdbthinsrv.
PAGE 22
A multi-process server is multi-threaded and may handle multiple concurrent clients allocating each client its own subprocess for database access, thus allowing better concurrency and availability. The majority of the multi-process server operations are carried out in a client thread context within the main server process, handing off control to the clients allocated subprocess only when direct Oracle Rdb database operations are required.
PAGE 23
Alternatively the controller may be used in command mode to start a server $ java –jar rdbthincontrol.jar –cfg mycfg.xml – –name Mpserv1 –startserver 3.2.1.2 Starting a Multi-process Server from Oracle SQL/Services A multi-process server may be started from Oracle SQL/Services manager. Using the Oracle SQL/Services manager, you must first establish a connection to the SQL/Service server. Once connected you may then start a JDBC dispatcher.
PAGE 24
Alternatively, you may wish to create a server definition within an XML-formatted configuration file and then start the server using its server name. Again the server type must be set to RdbThinSrvMP. Given the following server section in the XML-formatted configuration file mycfg.xml: the following method may be used to start this multi-process server: $ java –jar rdbthinsrv.jar –cfg mycfg.
PAGE 25
3.2.4 Executor Naming Each executor started up on a single system requires a unique process name on that system. By default a name will be created for the executor based on the name of the server that started it and a hexadecimal value that represents the instance of the executor process with relation to the server. The executor naming format may be changed by using the srv.execPrefix configuration option. If the srv.
PAGE 26
3.2.5 Executor Process Startup The multi-process server will create a subprocess for each executor it allocates and starts. OpenVMS command procedures are used during this subprocess creation. Information about these command procedures may found in the Server Command Procedures and On Start Commands sections of this document. If a persona is specified for the server (see Persona for more information ) the server will use the OpenVMS CREPRC system service to start the process.
PAGE 27
1. An executor name based on the server name is generated for the new process, see Executor Naming for more details. 2. Process quotas are determined for the executor process based on the current quotas of the executing server. 3. A termination mailbox is setup for the executor process and read issued. 4. CREPRC is used to create a process and SYS$SYSTEM:LOGINOUT.EXE is executed 5. Steps 3 thru 6 as described in the previous list above. 3.2.5.
PAGE 28
Subcode Description 30 65530 disk or directory is write protected , check the disk/directory pointed to by rdb$jdbc_com process terminated prematurely 65531 problem reading termination mailbox 65532 problem during call to CREPRC 65533 problem getting information about termination mailbox 65534 problem creating termination mailbox Note: It is important that the server process has appropriate access rights to the directories specified by JDBC$RDB_HOME, RDB$JDBC_COM and RDB$JDBC_LOGS logical names,
PAGE 29
3.3.1 Starting a Pool Server A pool server must be invoked on each node on which you wish to provide server redirection. The pool server does not need to be on the same node as its pooled servers. A pool server may be invoked by using the appropriate start statement within the controller, as an Oracle SQL/Services JDBC dispatcher or directly from the operating system command line. 3.3.1.
PAGE 30
See Oracle SQL/Services and Oracle JDBC for Rdb Servers for more details. Example $run sys$system:SQLSRV_MANAGE71 SQLSRV> connect server ; Connecting to server Connected SQLSRV> start disp JDBC_DISP; SQLSRV> See Oracle SQL/Services and Oracle JDBC for Rdb Servers for more details. 3.3.1.3 Starting a Pool Server from the Command Line You may invoke a pool server from the OpenVMS command line. Format $ java –jar rdbthinsrvpool.jar [-option ] See Pool Server Configuration Options for a list of valid options.
PAGE 31
You can use the srv.keepAliveTimer option on pool server start-up to specify the time between checks for non-running autoRestart servers. See Pool Server Configuration Options for more details. If the pool server is shutdown using the controller or the Oracle SQL/Services manager, then during server shutdown all pooled servers within the pool that have autoStart enabled will also be shut down. 3.3.2.
PAGE 32
Chapter 4 Server Configuration There are a number of configuration options that apply to Oracle JDBC for Rdb servers that may be used as command line options or as server options inside a configuration file. See Configuration Files for more details on how to use these options within a configuration file. The following sections detail the available configuration options. 4.
PAGE 33
Option Default Description By default, anonymous connections are disabled and the client must specify a valid username and password combination to access the Rdb database. allowDatabase none Specifies the name of a database this server will allow access to. This is used in conjunction with the restrictAccess option. This option should only be used within an XML formatted configuration file. The named database should also be described in the same configuration file.
PAGE 34
Option Default Description autostart false If specified, indicates to any pool server that may include this server in its pool of servers to automatically start up this pooled server. This option is only valid in an XML formatted Configuration File. See Pool Server Operation for more details. b or buffersize see Provides a hint to the server on sizing description of the underlying network I/O buffers.
PAGE 35
Option Default Description If the file extension is XML the configuration parameters are held in a XML format. See Configuration Files for more details. By default no configuration file is used. cli.idleTimeout 0 Sets the maximum time ( in milliseconds) a client connection may be idle. If no operation is carried out using this connection within the time specified, the connection will be forcibly disconnected. A value of zero (0) means unlimited idle time allowed.
PAGE 36
Option Default Description Exception. A value of minus one (-1) means wait indefinitely. log or logfile console Specifies the file specification of the log file for this server. If trace is enabled the trace messages will be written to this file instead of the console. By default trace messages will be written to the console. maxclients -1 Specifies the maximum number of concurrent clients this server instance may handle.
PAGE 37
Option Default Description pw or password none Used in conjunction with the user and anonymous switches provides the password to use on an anonymous connection persona none Specifies the Operating system username, which the process running the server will assume. If not specified persona will not be used. See Persona for more details.
PAGE 38
Option Default Description srv.execPrefix see Only valid for multi-process servers. description Specifies the prefix to use for executor names. If not specified a standard prefix base of server name will be used. See Executor Naming for more details. srv.execStartup see Only valid for multi-process servers. description Specifies the startup batch or command file that will be used to startup the subprocess for each client connection.
PAGE 39
Option Default Description Specifies the time in milliseconds to wait between server/executor handshake synchronization tries. srv.onExecStartCmd none Specifies a DCL command statement that should be executed prior to starting up an executor. See On Start Commands for more details. srv.
PAGE 40
Option Default Description Valid values are: • RdbThinSrv standard thin server • RdbThinSrvSSL thin server using SSL for communication • RdbThinSrvMP multi-process server • RdbThinSrvMPSSL – multiprocess server using SSL • RdbThinSrvPool pool server • RdbThinSrvPoolSSL pool server using SSL u or user none Used in conjunction with the password and anonymous switches provides the username to use on an anonymous connection url none Specifies the node IP and port thi
PAGE 41
Option Default Description controlpass none Specifies the password that control users must use to be able to issue control commands on this server instance. See Control Password for more information on this password. log or logfile console Specifies the file specification of the log file for this server. If trace is enabled, the trace messages will be written to this file instead of the console. By default trace messages will be written to the console.
PAGE 42
Option Default Description srv.keepAliveTimer 60 Sets the time (in seconds) of the duration between pool server checks for nonrunning pooled servers that have autoRestart enabled. See Oracle JDBC for Rdb Pool Server for more details. srv.mcBasePort 5517 Specifies the base port number that will be used for multicast operations. A value of zero (0) will disable multicast operations. srv.mcGroupIP 239.192.1.
PAGE 43
See Configuration Files for examples of configuring a pool server. Contents 4.3 Configuration Files Instead of setting the switches on the command line, you can specify a configuration file that details the settings. Two formats of configuration files are recognized: Standard Java Properties load file XML-formatted file 4.3.1 Standard Properties File The following section describes the use of configuration file formatted as a standard Java Properties load file.
PAGE 44
anonymous=true // enable password display showpass=true // limit the number of clients maxclients=10 // set the locking keywords lockwait=2 maxtries=20 // end of config file In addition, the configuration file for a thin pool server should contain information about the list of thin servers to which it may delegate connection requests, for example: // // configuration file for pool server // // the default port for the pool server is 1702 port=1702 // show is a pool server and the poolsize ( number of subse
PAGE 45
The XML-formatted configuration file differs from the standard CFG file in that it may contain information about multiple servers in the same configuration file. Each server is specified within a separate server section and must be given a unique name. This name is used to get default configuration information about the server on server startup, as well as how a server may be identified on your system and within the controller interface.
PAGE 46
restrictAccess = "false" anonymous = "false" bypass = "false" tracelocal = "false" relay = "false" controlUser="control_user" controlPass="0x18E007C81F6B2E2EA02065F78A587BD3" cfg="rdb$jdbc_com:rdbjdbccfg.xml" srv.execStartup="rdb$jdbc_home:rdbjdbc_startexec.com" srv.startup="rdb$jdbc_home:rdbjdbc_startsrv.com" sharedmem = "0" />
PAGE 47
PAGE 48
This section describes session characteristics for an interactive session. Information within the session section is currently only used by the Oracle JDBC for Rdb controller. You can specify information such as passwords and user names that may be used when you start up a controller session. Currently the controller will recognize only one session, which must be named DEFAULT. These session properties provide an alternate way of specifying the options other than command line options at controller startup.
PAGE 49
Option Default Description ssl.* none Specifies SSL configuration information for the session that may be used to connect to SSL-enabled thin servers. See Using SSL for more information Example Note: 1. The session properties srv.mcBasePort and srv.mcGroupIP specify the multicast attributes that should be used for polling servers.
PAGE 50
Format [ database property /> ] Options Valid properties for the database section can be seen in Table 4–4 Database Section Properties. Table 4-4 Database Section Properties Option Default Description name none This is the name by which the Oracle JDBC for Rdb drivers may recognize this database. This name is required and must be unique within the databases section of this configuration file. url none driver none This is the url that may be used to access this database.
PAGE 51
4.3.2.5 Servers Section This section specifies one or more server property sections. Format [ server section ] 4.3.2.6 Server Section This section specifies one or more properties to assign to this server. See Server Configuration for details on the properties that may be set.
PAGE 52
/> name="DEFAULT" type="RdbThinSrv" url="//localhost:1701/" maxClients="-1" srv.bindTimeout="0" srv.idleTimeout="0" srv.mcBasePort="5517" srv.mcGroupIP="239.1.1.1" autoStart="false" controlUser="jdbc_user" controlPass="0x811B15F866179583EB3C96751585B843" The DEFAULT and DEFAULTSSL server definitions should only be used to define the default server characteristics and are not intended to represent actual server instances that can be started by the controller or pool servers.
PAGE 53
The set of pooledServers provided will make up the pool of servers that the parent pool server may try to access. Format Example 4.3.2.8 Allowed Database Subsection This subsection specifies the database that clients using the server may access.
PAGE 54
This subsection specifies the usernames the server will allow access to. The declared username must be a valid username recognized by Rdb. The matching of usernames by the server for this level of restriction is not case-sensitive. The subsection is only valid when used within a server declaration. Multiple AllowUser subsections may be present in a single server declaration. For user access to be restricted the server attribute restrictAccess must be set "true".
PAGE 55
Because the LOGINOUT.EXE program is run, any file specification using relative file paths must be relative to the login directory of the invoker, otherwise a full file specification must be used.
PAGE 56
Chapter 5 Using SSL Secure Sockets Layer (SSL) was developed to provide security for Web traffic. Including confidentiality, message integrity, and authentication. SSL achieves this through the use of cryptography, digital signatures, and certificates. Oracle JDBC for Rdb servers and thin clients may use SSL for communication over TCP/IP.
PAGE 57
Example Properties info = new Properties(); info.put("user", user); info.put("password", password); info.put("tracelevel", traceLevel); info.put("ssl", "true"); info.put("ssl.default", "false"); info.put("ssl.context", "TLS"); info.put("ssl.keyManagerFactory", "SunX509"); info.put("ssl.keyStoreType", "jks"); info.put("ssl.keyStore", "rdbjdbccli.kst"); info.put("ssl.keyStorePassword", "CHANGETHIS"); info.put("ssl.trustStore", "rdbjdbccli.kst"); info.put("ssl.
PAGE 58
5.1.2 Server SSL Configuration An SSL-enabled server must also be provided with SSL configuration information. This is usually provided within the server section for the named server in an XML-based configuration file. To indicate that the server should be SSL-enabled, the server must be defined as one of the following SSL server types: RdbThinSrvSSL RdbThinSrvMPSSL RdbThinSrvPoolSSL Example
PAGE 59
See SSL Configuration Options for details of these options. Note: If a pool server is SSL-enabled, for security reasons it will only communicate with pooled servers within its pool that are also SSL-enabled. Non-SSL-enabled pooled servers within the pool will be ignored and will not be considered candidates for redirection of connection requests.
PAGE 60
ssl.context="TLS" ssl.keyManagerFactory="SunX509" ssl.keyStoreType="jks" ssl.keyStore="rdbjdbccli.kst" ssl.keyStorePassword="CHANGETHIS" ssl.trustStore="rdbjdbccli.kst" ssl.trustStorePassword="CHANGETHIS" /> This is the same SSL information that you would have provided for a client SSL configuration as described in Client SSL configuration. If this information is provided, the controller will use the SSL configuration to connect to any server that responds to a poll request as an SSL-enabled server. 5.
PAGE 61
Option Default Description "jks". ssl.keyStore none Indicates the filename of the keystore. ssl.keyStorePassword none Indicates the password for the keystore. ssl.trustStore none Indicates the filename of the trust store. ssl.trustStorePassword none Indicates the password of the trust store. 5.
PAGE 62
-file client.cer –keystore rdbjdbccli.kst $! $!---------------------------------------------------------------------$! $! Exchange the certificates by copying the client certificate file (client.cer) to $! The server node, and the server certificate file (server.
PAGE 63
Chapter 6 Oracle JDBC for Rdb Controller The Oracle JDBC for Rdb controller (here-on referred to as the controller) allows basic management of Oracle JDBC for Rdb servers. Contained in the rdbthincontrol.jar file, this application allows local and remote passwordprotected server management operations to be carried out on a thin server or pool server. These operations can include showing the clients that are currently connected, stopping client threads, and starting and stopping thin servers.
PAGE 64
RDB$ALLOWS_ANON RDB$ALLOWS_BYPASS RDB$NUMBER_OF_CLIENTS RDB$MAX_CLIENTS : : : : false false 0 -1 RDB$NODE RDB$PORT RDB$STATUS RDB$SERVER_NAME RDB$SERVER_TYPE RDB$SERVER_VERSION RDB$SERVER_SHR_VERSION RDB$SERVER_PID RDB$ALLOWS_ANON RDB$ALLOWS_BYPASS RDB$NUMBER_OF_CLIENTS RDB$MAX_CLIENTS : : : : : : : : : : : : localhost 1701 not available SRV1 RdbThinSrv not available not available not available false false 0 -1 RDB$NODE : localhost RDB$PORT : 1701 RDB$STATUS : not available RDB$SERVER_NAME : DEFAULT R
PAGE 65
RDB$NODE : 138.1.14.91 RDB$PORT : 1702 RDB$STATUS : Idle RDB$SERVER_NAME : srv2 RDB$SERVER_TYPE : RdbThinSrv RDB$SERVER_VERSION : T7.2-510 20070109 B719 RDB$SERVER_SHR_VERSION : T7.2-510 20061221 B6CL RDB$SERVER_PID : 0x2033137C(540218236) RDB$ALLOWS_ANON : false RDB$ALLOWS_BYPASS : false RDB$NUMBER_OF_CLIENTS : 0 RDB$MAX_CLIENTS : -1 RDB$TRACE_LEVEL : 0 RDB$LOG_FILE : rdbjdbclog RDB$RESTRICT_ACCESS : false rdbthincontrol> poll Polling servers ... srv2(0) //138.1.14.
PAGE 66
rdbthincontrol> poll Polling servers ... rdbthincontrol> exit 6.1 Running the Controller The controller allows basic management of Oracle JDBC for Rdb servers. The controller can be run from the OpenVMS DCL command line either in single command mode or as a command line interface: Format $java –jar rdb$jdbc_home:rdbthincontrol.jar [
PAGE 67
-controlpass none Specifies the control password to use when connecting to servers. This password takes precedence over any password option provided on the same command line -n or –node none Specifies the node where the server to be connected to is running. -name none Specifies a name for the server. The name is used to lookup server information within the start-up configuration file. The value of this name is not case-sensitive.
PAGE 68
session The value zero (0) means no tracing. -u or –user .none -url none Specifies the username to use for connection to the server Specifies the node IP and port of the server to connect to. This switch overrides any port and node switch specified The format of the is //:/ Note: A number of these options may also be specified in a session section of the XML-formatted configuration file used to start an interactive controller session.
PAGE 69
given on the command line See Stopping Servers for more information -stopclient Issues the Stop Client command which requests the connected server to terminate the specified client thread. The is an id of a client as displayed by the Show Clients command See Stopping Clients for more information.
PAGE 70
The commands that may be issued once a connection has been established to a server are discussed in Commands requiring server connection. The commands that do not require a server connection are discussed in Commands Not requiring a server connection. Format $java -jar rdb$jdbc_home:rdbthincontrol.jar –cfg my_servers.xml 6.1.2.
PAGE 71
stop client Stops the client matching the specified on the currently connected server. See Stopping Clients for more details. stop clients Stops all clients on the currently connected server. See Stopping Clients for more details. stop server Stops the currently connected server watch [server] Send trace logging from connected server to the current console. See Watching Servers for more details Example $java -jar rdb$jdbc_home:rdbthincontrol.
PAGE 72
Servers for more details. set session controlpass Sets the sessions control password. See Control Password for more information. set default tracelevel Sets the default tracelevel on the identified active server. This does not affect currently connected clients. Only clients connecting after the set default tracelevel is issued will be affected. set logfile Sets the logfile for the identified active server.
PAGE 73
stop active servers stop all servers stop server Stops active servers. See Stopping Servers for more details. open active servers open all servers open server Opens active servers. See Opening Servers for more details. close active servers close all servers close server Closes active servers. See Closing Servers for more details. watch [server] Watches active servers. See Watching Servers for more details.
PAGE 74
If a server does not recognize the provided control password, it will respond with a failure message: rdbthincontrol> show active servers Failed to connect No Rdb Thin Server connection has been established Unable to connect to server //localhost:1701/ Failed to connect No Rdb Thin Server connection has been established Unable to connect to server //localhost:1711/ rdbthincontrol> Contents 6.
PAGE 75
[] The string can be one of the following: • • • Port ID - this is the same as issuing //localhost:/ full URL with the format: //:/ name of server as found in the configuration used to start the controller The is: [] The must match the control password of the server for the control connection to be carried out successfully.
PAGE 76
This command connects to the server specified by the information.
PAGE 77
Example rdbthincontrol> stop server myMPServer Failed to connect No Rdb Thin Server connection has been established Unable to connect to server //localhost:1788/ In addition the control password may be set for a session by using the Set Session Controlpass statement at the controller command line prompt.
PAGE 78
The configuration file contents used for these examples may be seen in Sample configuration file MY_SERVERS.XML 6.4.1 Closing Servers Active servers may be closed using the controller. You must provide a valid control password for the server. Closing a server sets its maxClients attribute to zero (0) thus preventing any further connections to be made. Already established connections are not affected.
PAGE 79
6.4.1.2 Command mode The command mode commands available to close servers can be seen in Table 6–6 Command Mode Close Server. Table 6-6 Command Mode Close Server Command Description -closeServer-active -closeServer -all Closes all responding servers. The following Command line options, if present will be ignored: • • • • -name -node -port -URL -all and –active in this context are considered synonyms.
PAGE 80
The control commands available to open servers can be seen in Table 6–7 Interactive Open Server. Table 6-7 Interactive Open Server Command Description open active servers open all servers Opens all responding servers. open server Opens the currently connected server open server Opens the active server specified by the server connection information.
PAGE 81
Command Description -openServer Opens the server as specified by other command line options Example $ thincontrol –openServer –url //prod_node:1766/ $ thincontrol –openServer –port 1701 –node localhost $ thincontrol –openServer –active $ thincontrol –openServer –name myserver 6.4.3 Showing Servers Information about active and known servers may be displayed using the controller. You must provide a valid control password for the server before information will be displayed.
PAGE 82
Command Description by the server connection information. See Connecting to servers for more information Example rdbthincontrol> show server rdbthincontrol> show server myserv rdbthincontrol> show server //prod_node:1766/ rdbthincontrol> show server 1701 rdbthincontrol> show active servers rdbthincontrol> show server //prod_node:1766/ fred mypass 6.4.3.2 Command mode The command mode commands available to show servers can be seen in Table 6–10 Command Mode Show Server.
PAGE 83
Note: If multiple conflicting keywords are found on the one command line only one action will be taken and the following precedence is used • • • • -all -active -stored specified server Example $ thincontrol –showServer –url //prod_node:1766/ $ thincontrol –showServer –port 1701 –node localhost $ thincontrol –showServer –active $ thincontrol –showServer –all $ thincontrol –showServer –stored $ thincontrol –showServer –name myserver 6.4.4 Starting Servers Servers may be started using the controller.
PAGE 84
Only those servers that have the autostart attribute and are for the local host will be started. start server Starts a server of type RdbThinSrv on the local host with all default characteristics. start server Starts a server of type RdbThinSrv listening on the designated port on the local host with default remaining characteristics start server Starts the server that matches the name provided. See XML formatted Configuration File for more information on named server definitions.
PAGE 85
-startServer Starts the server as specified by other command line options Example $ thincontrol –startServer –port 1701 –node localhost $ thincontrol –startServer –name myserver $ thincontrol –startServer –all 6.4.5 Stopping Servers Active servers may be stopped using the controller. You must provide a valid control password for the server. Only those servers where the control password matches the control session control password will be stopped.
PAGE 86
Example rdbthincontrol> stop server rdbthincontrol> stop server myserv rdbthincontrol> stop server //prod_node:1766/ rdbthincontrol> stop server 1701 rdbthincontrol> stop active servers rdbthincontrol> stop server //prod_node:1766/ fred mypass 6.4.5.2 Command mode The command mode commands available to stop servers can be seen in Table 6–14 Command Mode Stop Server. Table 6-14 Command Mode Stop Server Command Description -stopServer -active -stopServer -all Stops all responding servers.
PAGE 87
6.4.6 Watching Servers The trace output for an active server may be displayed on the controller console. You must provide a valid control password for the server to be able to watch its trace. Only those servers where the control password matches the control session control password will be watched. When you watch a server, all trace output from that server will also be sent to the current console running the controller.
PAGE 88
6.4.7 Polling Servers The poll command uses the multicast information to poll responding Oracle JDBC for Rdb servers: Each available server will respond with information about which node and port it is listening on. In addition the poll response identifies the Process ID the server is using on that node. A control password is not required to use the poll command. 6.4.7.1 Interactive mode The control commands available to start servers can be seen in Table 6–16 Interactive Poll Server.
PAGE 89
Example $ thincontrol –poll 6.4.7.3 Multicast Polling The controller uses multicast polling to discover Oracle JDBC for Rdb servers that may be available on the network. Multicasting is a style of efficiently broadcasting data over a network connection to many connected servers. Any server listening in to the multicast IP address will receive the data packets that are broadcast, such as poll requests.
PAGE 90
Please consult your network manager to ensure that multicast polling is allowed on your system. Your network manager may also allocate a specific IP address and Port range that may be used by the Rdb Native Drivers, and you should change your server and session configuration files to reflect these allocated addresses. Setting the Multicast Base port to zero (0) will effectively disable multicast broadcast and receipt for that server.
PAGE 91
Example rdbthincontrol> show active clients rdbthincontrol> show all clients fred rdbthincontrol> show clients rdbthincontrol> show clients in disk1:[dbc]pers rdbthincontrol> show all clients in disk1:[dbc]pers 6.5.1.2 Command mode The command mode commands available to show clients can be seen in Table 6–19 Command Mode Show Clients. Table 6-19 Command Mode Show Clients Command Description -showclients Shows all clients on responding servers.
PAGE 92
If a database file specification is used, then only those clients current connected to that database will be stopped. The database file specification must match exactly (ignoring character case) to that shown in the Show Client output. Note: Stopping a client will forcibly terminate all database connections on that server for that client and does not wait for client transaction completion. You may use Show Clients command to see clients that are currently using the server.
PAGE 93
rdbthincontrol> stop client 0000000A rdbthincontrol> stop all clients in disk1:[dbs]pers 6.5.2.2 Command mode The command mode commands available to stop clients can be seen in Table 6–21 Command Mode Stop Clients. Table 6-21 Command Mode Stop Clients Command Description -stopClient Stops specified client on the currently connected server.
PAGE 94
-in -stopClients –in The following Command line options, if present will be ignored: • -name • -node • -port • -URL Stops all clients on the currently connect server that are currently connected to the specified database Example $ thincontrol –stopClient 0000000A $ thincontrol –stopClients –all $ thincontrol –stopClients –active –in db_dir:mf_personnel $ thincontrol –stopClients Contents 94
PAGE 95
Chapter 7 Oracle SQL/Services and Oracle JDBC for Rdb Servers The Oracle SQL/Services management command line may be used to start and stop servers using the new dispatcher protocol called JDBC available in Oracle SQL/Services V7.1.6 and later. Currently the Oracle SQL/Services interface to Oracle JDBC for Rdb Servers is minimal and may only be used to start and stop a JDBC dispatcher which in turn will start or stop the associated Oracle JDBC for Rdb server.
PAGE 96
To be able to start and stop Oracle JDBC for Rdb servers using Oracle SQL/Services, a dispatcher with protocol JDBC must be defined using the Oracle SQL/Services management console. You must provide the new dispatcher with a unique name and network_port. It is important to ensure that the use of the POR_ID is unique as the port provided will be used by the associated Oracle JDBC for Rdb server and only one server at a time may listen on a single TCPIP port.
PAGE 97
Each Oracle SQL/Services JDBC dispatcher must be associated with an Oracle JDBC for Rdb server. The PORT_ID specified in the dispatcher creation is the key to this relationship. The PORT_ID specifies the TCPIP port that will be used by the Oracle JDBC for Rdb server and is used by the dispatcher start up procedures to determine information about the associated server.
PAGE 98
A server name is required as it may be used by the server start up procedure to locate properties from its configuration file. The name used will determine various characteristics of the started server. In addition the server name will be used as the OpenVMS process name and will determine the naming of any associated executors if the server is a Multi-Process server. The server name is also used in creating log and temporary files during the running of the server.
PAGE 99
This command procedure will setup some environmental elements and then execute a JAVA command to start the server. A discrete dispatcher process will be set up by the SQL/Services START DISPATCHER command and the JAVA command will be run under this process context. The RDBJDBC_STARTSRV command procedure will try to locate and execute any specific setup command procedures you may have designated for its use.
PAGE 100
The PORT_ID is also used to determine the configuration file to use on server startup. This file can be a CFG or an XML-formatted configuration file and is used to provide information to the server about what characteristics it should use when running. See Configuration Files for more details on the use of configuration files.
PAGE 101
7.1.2.4 Determining Server Type During the startup of the server associated with the Oracle SQL/Services JDBC dispatcher, the type of the server to startup also needs to be determined. The server type will be used by the dispatcher to determine the appropriate JDBC JAR file to use when invoking the server. The server type will also used to determine other server attributes that have to be set for a successful instantiation of a server process.
PAGE 102
$ DEFINE/SYSTEM RDB$JDBC_SQSTYPE_1888 MP $ MCR SQLSRV_MANAGE71 SQLSRV> CONNECT SERVER; SQLSRV> CREATE DISPATCHER JDBC_DISP NETWORK_PORT TCPIP PORT_ID 1888 PROTOCOL JDBC; This will create a server with type RdbThinSrvMP. 7.1.
PAGE 103
For example: SQS_MALIBU_SQLSRV_DIS06010.LOG This log can be useful in determining why a dispatcher did not start up properly. For example if appropriate logical names have not been setup as specified in the installation of Oracle JDBC Drivers for Rdb then a message similar to the following may be found at the end of the log file: . . . $ @rdb$jdbc_home:rdbjdbc_startsrv SQS1880 "SQS" %DCL-E-OPENIN, error opening RDB$JDBC_HOME:[SYSMGR]RDBJDBC_STARTSRV.
PAGE 104
SYS$MANAGER:SQLSRV_JDBC_SERVER_STARTUP.COM to start the server associated with a JDBC dispatcher. As multiple versions of SQL/Services may be present on your system, the Oracle JDBC for Rdb installation provides multiple versions of the SQLSRV_JDBC_SERVER_STARTUP command procedure. The of the command procedure determines the version of SQL/Services it is associated with, thus: SYS$MANAGER:SQLSRV_JDBC_SERVER_STARTUP71.COM will be the command procedure used by version 7.
PAGE 105
7.3 Using Pool Servers Each JDBC dispatcher defined is related only to a single server. Use a pool server if you require more than one server to be started for a single dispatcher. By defining a pool of servers that the pool server can use and enabling autoStart on each of these servers, a whole pool of servers can be started by starting a single dispatcher. See Pool Server Operation for more information on pool servers.
PAGE 106
name="SQSrjs1" type="RdbThinSrv" url="//localhost:1891/" autoStart="true" maxClients="10" />
PAGE 107
The Oracle SQL/Services JDBC dispatcher SQS_ONSTARTUP command procedure is used during the startup of the associated pool server. Those servers that the pool server starts up use the command procedure pointed to by the srv.onStartCmd switch. The Oracle SQL/Services JDBC dispatcher does not directly use any information from the JDBC XML configuration file.
PAGE 108
Chapter 8 Other Features 8.1 Anonymous Usernames By default, the thin driver disallows blank usernames to be passed to it during database connection. A valid username for that database must be used. If the client attempts to connect to the database using a blank username the following exception will be raised: rdb.RdbException: Io exception : Io exception : in %RDB-E-AUTH_FAIL, authentication failed for user .Anonymous.
PAGE 109
The first pass at privilege checking occurs at an object identifier level, asking if this entity has the right to do this action to this object. If access is denied at this level a series of cascading attempts are made to try to get the privilege. After the object protection is checked, the entity's privilege at the database is checked. If the entity has been granted DBADM it will be allowed to carry out the operation even if it does not have the explicit privilege such as CREATE.
PAGE 110
When you use a Multi-process server a separate executor process is used to carry out the database operations. This executor process inherits the privileges and authorization characteristics from the server process that started it. Thus the information as described above applies to the executor processes in exactly the same manner as described for the server process. 8.
PAGE 111
When persona is used both the multi-process server and the pool server will need to have read/execute/write access to the RDB$JDBC_COM directory and read/write access to the RDB$JDBC_LOG directory. By default the installation of the JDBC drivers will create these directories on your installation destination directory and set the access to both these directories to world READ/EXECUTE. You will have to alter the file protection on these directories and grant WRITE access to the persona.
PAGE 112
behalf of the client. Please see the Oracle Rdb documentation for information on the default transaction mechanism provided by SQL. 8.5 Executor Sub-process used with the Rdb Native driver To improve multi-threaded concurrent access to the database while using the Rdb Native driver, you may specify that separate sub-process executors should be started for each connection request.
PAGE 113
On some systems where the workload is heavy and particularly on single-cpu systems it is possible that after the sub-process is created the main process may attempt to establish the communication unsuccessfully. Depending on process and thread scheduling it is possible that the maximum number of attempts to establish handshake may occur before the subprocess is scheduled for execution.
PAGE 114
"jdbc:rdbNative:my_db_dir:pers@ignoreStatementCancel=true", user, pass); 8.8 Inactivity timeouts The amount of time either a client connection or a server may remain inactive before being forcibly terminated may be set using server and connection switches. 8.8.1 Client connection timeout The –cli.idleTimeout switch may be used to specify the amount of time in milliseconds that a connection may remain inactive before being closed down. The default value of 0 specifies that the time is indefinite, i.e.
PAGE 115
Connection conn = DriverManager.getConnection( "jdbc:rdbthin://bravo:1701/my_db_dir:personnel@cli.idleTimeout=36000 00",user, pass); When specified this way the timeout will only apply to this one connection. If a non-zero cli.idleTimeout is specified in both the server configuration and as a connection qualifier, the lesser of the two values will be used for that connection.
PAGE 116
When server is terminated by this timeout the following message will be logged in the server log: Server terminated due to inactivity 2006-02-08 12:28:03.578 : Forced disconnect by Server terminated due to inactivity @ LOCAL A server inactivity timeout will occur if, for the length of time specified, no new client connection is made to that server. In other words the timeout period is started after each new connection.
PAGE 117
The standard thin server is a multi-threaded server that allows concurrent access to Oracle Rdb by many client processes. Within a single OpenVMS process, Oracle Rdb is singlethreaded, thus the thin server has to synchronize client database activity. Because database actions must be serialized, any action that might take a prolonged length of time may seriously impact the overall throughput of the server.
PAGE 118
1. A connection lockwait value as specified explicitly on the connection string will take precedence over the server lockwait value but only for that one connection. 2. An explicit LOCKWAIT set on either the server or connection will take precedence over the value set by the RDM$BIND_LOCK_TIMEOUT_INTERVAL logical name. 3.
PAGE 119
Results in a lockwait of 10. See your Oracle Rdb Documentation for more information on the use of the RDM$BIND_LOCK_TIMEOUT_INTERVAL logical name and the LOCK TIMEOUT INTERVAL clause. 8.11 Logging Oracle JDBC for Rdb drivers and servers can now use the Java Logging utilities to log error messages and trace information. By default Java Logging is turned off. See your Java JDK 1.4.1 for information on the Java Logger. 8.
PAGE 120
The following two special server names may be used, DEFAULT and DEFAULTSSL, within the XML-formatted configuration file. The server characteristics defined in the DEFAULT server will be used to provide the base configuration information for all servers, but any of these characteristics can be over-ridden either by command line switches or by characteristics defined within the specified named server in the configuration file. Example 2 For example given the following server entry in MY_CFG.
PAGE 121
and the following command line statement: $ java -jar rdbthinsrv.jar -cfg MY_CFG.XML -name "myServer" A thin server with the name myServer will be started up on localhost listening to port 1799 with unlimited maxClients. Contents 8.13 Named Databases The XML-formatted configuration file allows the specification of known named databases, allowing the Oracle JDBC for Rdb servers the ability to recognize alternate names for databases served on the node the server is running on.
PAGE 122
8.14 On Start Commands There are two startup command attributes that may be specified in the XML-formatted configuration file server section: srv.onStartCmd and srv.onExecStartCmd. These options allow the specification of DCL command that should be executed just prior to the start up of a server or executor. Note: The srv.onStartCmd and the srv.onExecStartCmd point to a command that will be execute on start up of the server or executor.
PAGE 123
/> srv.onStartCmd="@rdb$jdbc_com:our_setup.com" Care should be taken when providing commands for the server process to execute using this property. These commands will be executed prior to the invocation of the Java statement that starts the actual server instance. As detached OpenVMS processes will be used to run the server you must ensure that all the necessary symbols and logical names are available for the server's use within the detached process.
PAGE 124
8.14.2 srv.onExecStartCmd This option specifies a DCL command to be executed prior to the invocation of an executor by a multi-process server. It must be a valid OpenVMS DCL command and must be valid within the context of the multi-process server process. If multiple DCL commands are required, then they should be placed within a DCL command procedure, which in turn should be made available to the environment under which the server runs.
PAGE 125
To ensure better security of these passwords, the server configuration file may contain the server control password in an obfuscated form. Example 1 For example, in an XML-formatted server configuration file: You can obtain an obfuscated password by using the Digest statement in the Rdb Thin Server Controller.
PAGE 126
Obfuscated passwords are only valid when used in conjunction with a server definition in a configuration file or as a server start up command line configuration option. To connect to the server as a control user to carry out operations on it using the controller, the control password you use in the connect request must still be in plain text. You cannot use the obfuscated value as a password on connection. Contents 8.
PAGE 127
If the server restrictAccess property is false or not specified or if no allowDatabase subsection is specified for the server then no database restrictions will be applied. Example 1 For example given the above server description of a server running on the node bravo : Connection conn = DriverManager.getConnection( "jdbc:rdbThin://bravo:1701/mf_pers",user, pass); will be allowed. Example 2 Connection conn = DriverManager.
PAGE 128
Example The name value of an allowUser subsection must be a valid Rdb username. If a client intends to use a server with restricted user access, then the username used for the connection must match one of the names within the allowed user subsections. The username match is not case-sensitive.
PAGE 129
In this example, an obfuscated password is used which matches the plain text password "jdbc_user" To connect to a database using this server the client must provide a @srv.password value on the connection request and the password must be a plain text password that matched the one specified for the server. Connection conn = DriverManager.getConnection( "jdbc:rdbThin://bravo:1755/my_db_dir:pers@srv.password=jdbc_user", user, pass); 8.17 Scope of CONNECTION.
PAGE 130
rdb$jdbc_home:rdbjdbc_startsrv.com and the executor startup command procedure: rdb$jdbc_home:rdbjdbc_startexec.com Caution: Do not use the SET VERIFY command within these command procedures. As the method Runtime.exec() may be used by the servers to create processes, the use of the SET VERIFY command within the command procedure may hang the server. This is a documented limitation of using Runtime.exec() on Open VMS Java. The logical name JAVA$EXEC_TRACE is available to help debug Runtime.
PAGE 131
srv.startup=rdb$jdbc_home:rdbjdbc_startsrv.com You can choose to change this default command procedure to customize for your system settings, or you can create a new customized procedure and change the configuration file so that servers use this new file. However Oracle recommends that you use the srv.onStartCmd server attribute instead. See srv.onStartCmd for more information on using the srv.onStartCmd attribute. Caution: Do not use the SET VERIFY command within these command procedures.
PAGE 132
Caution: Do not use the SET VERIFY command within these command procedures. As the method Runtime.exec() may be used by the servers to create processes, the use of the SET VERIFY command within the command procedure may hang the server. This is a documented limitation of using Runtime.exec() on Open VMS Java. The logical name JAVA$EXEC_TRACE is available to help debug Runtime.exec() calls on OpenVMS. Refer to the OpenVMS Java documentation for more details. The srv.execStartup and srv.
PAGE 133
OpenVMS FailSAFE IP may be using in conjunction with Oracle JDBC for Rdb thin driver and servers. During failover, FailSAFE IP will redirect the existing Oracle JDBC for Rdb client/server IP connections to the standby service. If the failover service exists on the same node as the failed service the connections should continue to be viable transparently. If however, the failover service is on another node, then as Rdb connections cannot be transferred between processes, the failover will not be transparent.
PAGE 134
Format SET TRACELEVEL Sets the trace level, see Trace for more information. SET SQLCACHE Sets the SQL Statement cache size to the specified value. A value of 0 disables SQL statement caching. The SET statements can be issued as a SQL statement in the following methods: • • • java.sql.Statement.execute java.sql.Statement.executeUpdate java.sql.Statement.executeQuery Example Statement stmt = conn.createStatement(); stmt.execute(set sqlcache 10); 8.
PAGE 135
Example • Set the sqlcache property of the Properties passed to the DriverManager.getConnection method: Properties info = new Properties(); info.put("user", user); info.put("password", pw); info.put("sqlcache", 100); conn = DriverManager.getConnection (connStr, info); • Append @sqlcache to the database specification part of the connect URL: Connection conn = DriverManager.
PAGE 136
8.23 Trace Trace provides tracing of method calls and other debug information within the Oracle JDBC for Rdb drivers and servers. See Trace Values for valid trace level values. The trace level value may be a signed decimal or a Java-style hexadecimal literal. By default, trace output is written to the normal JDBC DriverManager PrintWriter. You can override the default by using one of the following settings: • • rdb.Debug.setLogStream(PrintStream ps) rdb.Debug.
PAGE 137
See Connection Options for more details. 8.23.1.2 Tracelevel Switch Using the tracelevel switch when starting a server can enable tracing: Example $java -jar rdb$jdbc_home:rdbthinsrv.jar -cfg thinsrv.cfg -tracelevel -1 See Starting a Thin Server from the Command Line, Starting a Multi-process Server from the Command Line and Starting a Pool Server from the Command Line for more details. 8.23.1.
PAGE 138
Example $java -jar rdb$jdbc_home:rdbthincontrol.jar rdbthincontrol> connect //localhost:1701/ jones mypassword rdbthincontrol> set tracelevel -1 See Controller Command Line for more details. 8.23.2 Abbreviated form of tracelevel The abbreviated form for the traceLevel keyword , "tl", may also be used in the same manner. 8.23.3 Trace Values The value passed to trace is actually a 32bit flag mask. Each bit set determines what will be traced, as shown in the following table.
PAGE 139
8.24 File and Directory access Requirements There are certain file and directory access requirements that must be met to successfully use Oracle JDBC for Rdb servers, drivers and the controller.
PAGE 140
If persona is used with servers then you must ensure that the persona has the appropriate access rights as described above.
PAGE 141
Chapter 9 JDBC Extensions for Oracle Rdb The following sections provide information on features that are extensions to the JDBC standard provided by Oracle JDBC for Rdb. 9.1 Enhanced Blob Handling The maximum size of a blob segment supported by Oracle Rdb today is 65535. The Oracle JDBC for Rdb drivers will correctly handle segments up to this maximum size.
PAGE 142
byte[] bytes = bl.getBytes(1,9999); String st1 = new String(bytes); System.out.println("resume : " + st1 ); The separator can be cleared by passing either a null object or empty String as the parameter to oracle.jdbc.rdb.common.Blob.setSegSeparator(). 9.2 ResultSet.getBytes() The JDBC standard limits the use of the ResultSet.getBytes() methods for access to BINARY, VARBINARY and LONGVARBINARY data.
PAGE 143
Chapter 10 Other Information 10.1 Disallowed Dynamic SQL Statements Because JDBC has its own connection protocol, the following dynamic SQL statements will raise an exception if they are executed from a Statement or PreparedStatement SET CONNECT CONNECT DISCONNECT 10.2 Sample Setup, Starting and Using an Oracle JDBC for Rdb thin server. This section describes step by step how you can start a simple JDBC server and use it to access a database on your system 1. 2. 3. 4. 5. 6. 7.
PAGE 144
correctly specified thus details of the appropriate command procedures will be provided below. Step 1 Install Oracle JDBC for Rdb The Oracle JDBC for Rdb Release Notes describe the steps required to install Oracle JDBC for Rdb. These steps should be followed to install the product on the OpenVMS node that will be used as server for you Oracle Rdb database. The server machine requires JAVA to be installed prior to installing the Oracle JDBC for Rdb kit.
PAGE 145
Both Rdb and JAVA provide mechanisms by which you can set up your environment for a specific version or variant. The Rdb version set up and the JAVA VM set up may be carried out manually by you prior to invoking a thin server from the DCL command line. Alternatively there are ways of providing the appropriate set up during the thin server start-up when you choose to start the server using SQL/Services JDBC Dispatcher or by using the controller.
PAGE 146
If you only require private use then JOB level logical names should be used, in which case the RDBJDBC_STARTUP.COM may be copied and/or modified to change the logicals to JOB level. Each user of the Oracle JDBC for Rdb on your server system will then need to invoke this startup procedure prior to carrying out operations such as controller actions, starting or stopping or accessing the thin servers. The RDBJDBC_STARTUP.
PAGE 147
specification be available to that process. The easiest way to ensure this is to have system wide logical names. In addition a control password, MySecretPassword has been chosen for control access to the servers. Although the controlpass can be stored in its plain text form in the configuration file, Oracle recommends that you use the obfuscated form in the server characteristics section.
PAGE 148
anonymous = "false" bypass = "false" tracelocal = "false" relay = "false" srv.startup="rdb$jdbc_home:rdbjdbc_startsrv.com" /> Note The server definition for MY_SRV is fairly minimal allowing most of the DEFAULT characteristics to inherited.
PAGE 149
Example my_setup.com $@SYS$LIBRARY:RDB$SETVER 72 $@sys$common:[java$142.com]JAVA$142_SETUP.COM FAST $define/job MY_DB_DIR sys$common:[DBS] These commands ensure that the environment is correct for the server process to access a V7.2 Oracle Rdb database using the FAST JAVA VM. Step 4 Start the Oracle JDBC for Rdb thin server Now that set up and configuration files are created in place the controller may be used to start the server.
PAGE 150
Once the Oracle JDBC for Rdb kit is installed on you OpenVMS server machine you must copy the thin driver component to the machine on which you will be running your application. This machine will also need to have JAVA installed. The client-side components of the thin driver are contained in the RDBTHIN.JAR file. A file transfer program such as FTP may be used to copy this JAR file to your client machine. Remember to ensure that a binary mode transfer is done as JARs are binary files.
PAGE 151
// You need to import the java.sql package to use JDBC import java.sql.*; // We import java.io to be able to read from the command line import java.io.*; class my_app { static BufferedReader in; public static void main(String args[]) throws SQLException, IOException, Exception { String driverConStr = "jdbc:rdbThin://555.1.14.91:1888/"; in = new BufferedReader(new InputStreamReader(System.in)); Class.forName ("oracle.rdb.jdbc.rdbThin.Driver"); // Prompt the user for connect information System.out.
PAGE 152
} // Utility function to read a line from standard input static String readEntry(String prompt) { try { StringBuffer buffer = new StringBuffer(); System.out.print(prompt); System.out.flush(); return in.readLine(); } catch(IOException e) { return ""; } } Step 7 Run your application With the server started you can run the sample application and provide the thin server connection information Example The following example assumes an Oracle Rdb database personnel in MY_DB_DIR $java –cp .;rdb$jdbc_home:rdbThin.
PAGE 153
3. Create a JDBC dispatcher in SQL/Services 4. Associate configuration and setup files 5. Start the JDBC dispatcher See Chapter 7 Oracle SQL/Services and Oracle JDBC for Rdb Servers for more information on these operations. Step 1 Decide on the versions of Rdb and JAVA This step is basically the same as Step 2 Decide on the versions of Rdb and JAVA, as covered in Sample Setup, Starting and Using an Oracle JDBC for Rdb thin server.
PAGE 154
about the various servers you may be running. In addition it provides session information for users of the controller. For this walkthrough we have decided to create the definition for a thin server called SQS1888 listening on port 1888. The generic configuration file was copied and changed to add this information.
PAGE 155
PAGE 156
default, this file will be used by the dispatcher whenever a server has to be started. JDBC Dispatcher Setup Procedure describes the use of a setup command procedure for the dispatcher. Example $type RDB$JDBC_COM:RDBJDBC_SQS_ONSTARTUP.COM $@SYS$LIBRARY:RDB$SETVER 72 $@sys$common:[java$142.com]JAVA$142_SETUP.COM FAST $define/job MY_DB_DIR sys$common:[DBS] These commands ensure that the environment is correct for the server process to access a V7.2 Oracle Rdb database using FAST JAVA VM. Step 3.
PAGE 157
Given the PORT_ID of 1888: • • • server name = SQS1888 configuration file = RDB$JDBC_COM:SQS1888_CFG.XML setup file = RDB$JDBC_COM:RDBJDBC_SQS_ONSTARTUP.COM If we had chosen not to use standard naming then we would have had to set up logical names to point to the appropriate files. See Associating an Oracle SQL/Services JDBC Dispatcher to a Server for more details. However, we still need to tell the dispatcher what type of server it will be starting so we have to create the appropriate logical name.
PAGE 158
TCP/IP port 1888 Inactive JDBC clients Log path: SYS$MANAGER: Dump path: SYS$MANAGER: Log File: SYS$SYSROOT:[SYSMGR]SQS_DECRDB_JDBC_DISP08O91.LOG; Dump File: SYS$SYSROOT:[SYSMGR]SQS_DECRDB_JDBC_DISP08O.DMP; See your Oracle SQL/Services documentation and Starting a JDBC Dispatcher for more details on starting a dispatcher. If the server starts up correctly you should be able to use the server from any JDBC client using the Oracle JDBC for Rdb thin driver.
PAGE 159
tracelevel = "0" autostart = "false" autorestart = "false" restrictAccess = "false" anonymous = "false" bypass = "false" tracelocal = "false" relay = "false" controlUser="control_user" controlPass="0x7315A012ECAD1059A3634F8BE1347846" cfg="rdb$jdbc_com:rdbjdbccfg.xml" srv.execStartup="rdb$jdbc_home:rdbjdbc_startexec.com" srv.startup="rdb$jdbc_home:rdbjdbc_startsrv.com" sharedmem = "0" ssl.default="true" />
PAGE 160
name="srvMPforRdb" type="RdbThinSrvMP" url="//localhost:1705/" autoStart="true" maxClients="10" maxFreeExecutors="10" prestartedExecutors="10" sharedMem="10240" />
PAGE 161
VARCHAR(n) NCHAR VARYING FLOAT[(n)] REAL DOUBLE PRECISION DECIMAL[(n[,n])] INTEGER[(n)] SMALLINT[(n)] TINYINT[(n)] BIGINT[(n)] QUADWORD[(n)] DATE ANSI DATE VMS TIME TIMESTAMP INTERVAL BYTE VARYING LIST OF BYTE VARYING VARCHAR VARCHAR If n > 24 then DOUBLE else FLOAT FLOAT DOUBLE DECIMAL If n == 0 then INTEGER else NUMERIC If n == 0 then SMALLINT else NUMERIC If n == 0 then TINYINT else NUMERIC If n == 0 then BIGINT else NUMERIC If n == 0 then BIGINT else NUMERIC DATE TIMESTAMP TIME TIMESTAMP BIGINT VARBINA
PAGE 162
TIME BIGINT VARBINARY BLOB CLOB TIME INTERVAL BYTE VARYING LIST OF BYTE VARYING LIST OF BYTE VARYING 10.7 JDBC Specification SQL to Java Datatype Mappings SQL Type (from java.sql.Types) BIT TINYINT SMALLINT INTEGER BIGINT REAL FLOAT DOUBLE DECIMAL NUMERIC CHAR VARCHAR LONGVARCHAR DATE TIME TIMESTAMP BINARY VARBINARY BLOB CLOB Java Type boolean byte short int long float double double java.math.BigDecimal java.math.BigDecimal java.lang.String java.lang.String java.lang.String java.sql.Date java.sql.
PAGE 163
10.8 JDBC Specification Java to SQL Datatype Mappings Java Type boolean byte short int long float double java.math.BigDecimal java.lang.String byte[] java.sql.Date java.sql.Time java.sql.Timestamp java.sql.Blob java.sql.Clob SQL Type (from java.sql.