Home

HP Driver for JDBC User`s Manual

1. 25 Example 3000 0 0 a 26 Configuring HP J DBC 5 27 Starting and Stopping HP JDBC 29 HP UX Monitor Startup and Shutdown 29 Monitor Startup and 5 30 4 Using the HP Driver for J DBC Java Class iia ane t ne ea eee ba pete eate ee aes 32 Example on the HP 90000 32 Sample J DBC CGlient ies eS ae pepe A ee Edu Pu ee dee 33 Loading the HP Driver DBC 0 cect ee 35 Connection Parameters ccc hs 36 URL Syntax Including User lt 5 38 URL Syntax Without User Name 39 HP UX Userid Password 0 ccc ccc etn eee hrs 40 MPE iX Userid Password 1 ccc eee mrs 41 5 ALLBASE SQL Specifics ALLBASE SQL toJ DBC Data Type 44 ALLBASE SQL to DBC Data Type Conversions 45 Unsupported ALLBASE SQL Data Types 47 Acceptable SQL Syntax PES ee ae Pe eee 48 Un
2. catch java lang Exception ex ex printStackTrace try rs close stmt close rs null stmt null catch Exception End of main loop try con close catch Exception e System exit 0 Print out SQLException information private static void printException SQLException e SQLException ex e System out println SOLException while ex null System out println SOLState ex getSQLState 84 Appendix D Simple Client Source Code System out printin Vendor code ex getErrorCode System out printin Message x getMessage X ex getNextException System out println return Displays all columns and rows in the given result set private static void displayResultSet ResultSet rs throws SQLException int i Get the ResultSetMetaData This will be used for the column headings ResultSetMetaData rsmd rs getMetaData Get the number of columns in the result set int numCols rsmd getColumnCount For each column print out the column names separated by commas ura for i 1 i lt numCols 1 System out print rsmd getColumnName i Print separator if i lt numCols System out print Appendix D 85 Simple Client Source Code
3. The sample spool file shows three client connections For each connection the client address is logged along with the time of the connection If the user validation fails the cause of the failure is logged If the user validation succeeds the connection to the DBC Server is logged Appendix B 73 JDBC Monitor HP UX Monitor Startup and Shutdown HP UX Monitor Startup and Shutdown The startup and shutdown of the HP UX J DBC Monitor is normally controlled by the system startup and shutdown scripts Thus as long as the machine is up and running the DBC Monitor will also be up The only time the monitor should ever be brought down is toinstall a newer version n this case use the monct r1 command to kill the monitor process Do not use the UNIX kill command as this could render the server unable to start a new monitor process The monctr1 command to shut down the monitor is monctrl kill portnumber The portnumber parameter is only necessary if the monitor you wish to shut down is not running on the default port number 31700 Torestart the monitor after it has accidentally terminated or was shut down use the monct c1 command to start it up monctrl start portnumber Again the portnumber parameter is only necessary if you wish to start the monitor on a port number other than the default port number 31700 74 Appendix B JDBC Monitor MPE iX Monitor Startup and Shutdown MPE iX Monitor Startup and Shutdo
4. J DBC Driver Manager HP Driver for J DBC Network HP J DBC Server ALLBASE SQL or IMAGE SQL Database The HP Driver for J DBC is a vendor specific DBC Driver that allows J ava applications to connect to ALLBASE SQL or IMAGE SQL databases on MPE iX or ALLBASE SQL on HP UX The HP Driver for J DBC is a Type 3 Network Protocol driver meaning the driver translates the DBC API into a DBMS independent protocol on the client side and then translates to the ALLBASE SQL protocol on the server The driver components on the client side are written in 100 ava which provides the complete compatibility with all J ava Virtual Machines on all platforms The document will only describe the specifics necessary to use the HP Driver for DBC and not J DBC andJ DBC drivers in general For a comprehensive discussion of J DBC refer to J DBC Database Access with J ava A Tutorial and Annotated Reference Graham Hamilton Rick Cattell and Maydene Fisher Addison Wesley 1997 12 Chapter 1 Introduction HP JDBC Components HP J DBC Components There are three components supplied with the HP J DBC product the J DBC Driver the DBC Monitor and the DBC Server The user is responsible for writing a J ava application or applet that uses DBC on the client as well as providing the ALLBASE SQL or IMAGE SQL database on the server TheHP Driver for J DBC is a set of J ava dasses that implement the java sql i
5. 24 x x X X X X XX X FLOAT 25 53 x x X X X X x x DOUBLE x x X X X xX X X PRECISION X X X X m x x DATETIME INTERVAL x lt x x x SBBBEEBE Chapter 5 45 ALLBASE SQL Specifics ALLBASE SQL to JDBC Data Type Conversions The conversion of any ALLBASE SQL data type to the java sql Types BIT data type is such that only the value of zero in either numeric or character format will be converted to the java sql Types BIT value of 0 All other values will be converted to the java sql Types BIT value of 1 Thus only the integer value 0 the floating point value 0 0 the decimal value 0 not 0 0 and the character string 0 will be converted to a bit value of Everything elseis converted to a bit value of 1 The conversion of an ALLBASE SQL data type toa J DBC data type that has a smaller degree of precision such as conversion from ALLBASE SQL INTEGER tO java sql Types SMALLINT Will follow the J ava VM rules of casting one data type to another This may result in different values on different J ava platforms Thereis also no warning generated when this occurs It is up to the application developer to choose the appropriate DBC data type The conversion of an ALLBASE SQL character data typeto DBC numeric data
6. statement is not supported since the user should be using the java sql Connection commit method Table 5 3 Unsupported Statements BEGIN DECLARE SECTION BEGIN WORK COMMIT WORK CONNECT DECLARE CURSOR DELETE WHERE CURRENT DESCRIBE DISCONNI END DECLARE SECTION EXECUTE EXTRACT FETCH INCLUDE PREPARE RELEASE LLBACK WORK SET CONNECTION SET SESSION ET TRANSACTION SETOPT START DBE STOP DBE SOLEXPLAIN UPDATE WHERE CURRENT IERMINATE USER WHENEVER Chapter 5 49 ALLBASE SQL Specifics Dynamic Parameterized SQL Statements Dynamic Parameterized SQL Statements The HP Driver for DBC supports the use of parameterized SQL statements through the java sql PreparedStatement interface The SQL statements must use a question mark as the marker character for passing the parameters For example a SELECT statement with parameters in the where clause would look like SELECT NAME ADDRESS FROM ADDRBOOK WHERE NAME Another example is an INSERT statement INSERT INTO ADDRBOOK NAME ADDRESS VALUES 2 2 50 Chapter 5 ALLBASE SQL Specifics Stored Procedures Stored Procedures The HP Driver for sup
7. 3996 bytes Space characters are used to pad any strings of length less than n to length n String of characters of variable length up tolength wheren isin therange 1 to 3996 bytes String of characters in the format YyvY MM DD where represents the calendar year is the month and DD is the day of month DATE is in the range from 0000 01 01 to 9999 12 31 String of characters in the format HH MI SS where represents hours MI is minutes and ss is seconds TIME is in therange 00 00 00 to 23 59 59 AppendixA DATETIME INTERVAL UJ INARY VARBINARY LONG BINARY LONG VARBINARY Appendix A Data Types ALLBASE SQL Data Types String of characters in the format YYYY MM DD HH MI SS FFF where represents the calendar year MM is the month DD is the day of the month is the hour is the minute SS is the second and rrr is the thousandth of a second DATETIME isin the range 0000 01 01 00 00 00 000 to 9999 12 31 23 59 59 999 String of characters in the format DDDDDDD HH MI SS FFF where DDDDDDD is a number of days HH is a number of hours M1 is a number of minutes ss is a number of seconds and FFF is a number of milliseconds INTERVAL is in the range 0 00 00 00 000 to 3652436 23 59 59 999 Binary string of fixed length n wheren i
8. From your client platform ftp hpux server gt binary gt get opt allbase jdbc driver tar driver jar Of driver zip Using Reflection Software See the software description on how to transfer the appropriate file transfer the file in binary mode Extracting the HP Driver for J DBC Class Files TheHP Driver for J DBC class files must be installed in your J ava class path so that the J ava compiler and the J ava class loader can find them Example on the Win32 Platform CLASSPATH C JDK1 1 4 LIB You would then install the HP Driver for J DBC class files in the directory C JDK1 1 4 LIB Each of the package formats include the directory information so they should be copied into the class path directory Once the package is copied onto the client machine extract the contents of the package by using the appropriate command or application for that package format For example tar command to unpack the TAR package Star xvf driver tar jar command to unpack the AR package jar xvf driver jar UNZIP or WINZIP to unpack the ZIP package Follow the unzip steps provided in the UNZIP WINZIP package For the HP 3000 system a job stream I01 IJDBC JDBC SYS is provided to install the HP Driver for J DBC client under the usr local java latest lib directory of the MPE IX POSIX domain Chapter 3 25 Installation Java Client Components Example on HP 3000 CLASSPATH u
9. INFO WARNING and ERROR Turns on logging of a lot of debugging messages This should only be done at the request of support personnel This generates a large amount of logging information and can severely affect performance For a sample of a J DBC Server log file refer to Appendix C HP DBC Server 60 Chapter 6 lt This appendix gives a detailed description of Data Types supported by ALLBASE SQL and HP Driver for DBC Data type names size and format are discussed 61 Data Types ALLBASE SQL Data Types ALLBASE SQL Data Types SMALLINT INT EGER NUMI ERIC IMAL n DOU BLE LOAT VARCHAR DAT 62 16 bit signed integer 32 bit signed integer Fixed point packed decimal number with a specified precision of p and scale of s where isin the range 1 to 27 s isin the range 0 top The default value of p is 27 and s is 0 Identical to NUMERIC 32 bit floating point number A floating point number with a precision of where p is in therange 1 to 53 The default value of p is 53 If the value of p is from 1 to24 the value is treated as a 32 bit floating point number If the value of p is from 25 to 53 the value is treated as a 64 bit floating point number 64 bit floating point number String of characters of fixed length n wheren isin therange 1 to
10. System out println Nn Print out the data fetching until end of the result set String colValue while rs next For each column print out the data as String for 1 1 i lt numCols i colValue rs getString i If data was null use the null string instead if rs wasNull colValue lt NULL gt System out print colValue Print separator if i lt numCols System out print System out printin Fetch the next result set row System out printlin This captures console input until a CR is hit and then returns a Java String with the input 86 Appendix D Simple Client Source Code private static String getString String prompt BufferedReader in new BufferedReader new InputStreamReader System in if prompt null System out print prompt String rString try rString in readLine catch Exception e rString return rString Appendix D 87 Simple Client Source Code 88 Appendix D HP ALLBASE SQL J DBC File Lists This appendix lists each file that is provided in the HP J DBC product and gives a brief description of the purpose of the file 89 HP ALLBASE SQL JDBC File Lists Java Client Files J ava Client Files In the com hp jdbc allbase directory Jdbc class Parent J DBC Driver dass JdbcCallableStatement class J DBC callable statement
11. file This log file was generated using the LOGGING CONNECTION LOGGING IN and LOGGING TIMESTAMP logging levels set in the server configuration file 152 Connected to SOLID DM on Wed Oct 7 12 26 14 156 Connected from 15 0 121 172 by lt unavailable gt 243 TID SET_LIB_OPTIONS 443 TID CONNECT 445 connect to db dbname tmp db mydb 638 connect to db Connected 843 TID SET OPTIONS 844 TID SET OPTIONS datalen 8 data 0x0000010000000100 43 TID SET OPTIONS 44 TID SET OPTIONS datalen 8 data 0x0001000000010000 55 TID GET DBMS TYPE 261 TID GET INFO 644 TID GET METADATA 645 get metadata metadatatypestr INFO 844 TID GET METADATA 845 get metadata metadatatypestr max values 643 TID PREPARE SQL 645 TID PREPARE inbuf select from mytest 655 After Parse stmt buf2 SELECT FROM mytest 736 TID EXECUTE SQL 391 TID DISCONNECT Disconnected 392 Client exited 392 Connection ended on Wed Oct 7 12 26 45 This sample shows a single client connection connecting to the tmp db mydb database issuing the SQL statement SELECT FROM MYTEST and disconnecting The beginning of the file shows when the server was started and the P address of the client The first column shows the UNIX process id number PI D of
12. implementation class JdbcConnection class J DBC connection implementation class JdbcDatabaseMetaData class J DBC database metadata implementation class JdbcDBMD class J DBC database metadata utility class JdbcDriver class J DBC Driver implementation class JdbcPreparedStatement class J DBC prepared statement implementation class JdbcResultSet class J DBC result set implementation class JdbcResultSetMetaData class J DBC result set metadata implementation class JdbcStatement class J DBC statement implementation class JdbcVersion class J DBC driver version information class LogonDialog class J DBC driver logon dialog box utility class ThreadInfo class J DBC driver thread information dass Tracing class J DBC driver tracing utility dass 90 Appendix E HP ALLBASE SQL JDBC File Lists Java Client Files In the com hp jdbc allbase samples directory README Samp eC J DBC sample client readme file ient java Simp eC J DBC sample dient source code ient java J DBC simple client source code very simple Appendix E 91 HP ALLBASE SQL JDBC File Lists HP UX Server Files HP UX Server Files In the opt allbase jdbc directory bin jdbcmon J DBC Monitor executable program bin moncfg J DBC Monitor text configuration file bin monctrl J DBC Monitor control file bin jdbcserv J DBC Server executable program bin servcfg J DBC Server text configurat
13. password user subo 27578 Exiting 27152 PID 27578 died 0 left 70 Appendix B JDBC Monitor HP UX Monitor Log The sample log file shows three client connections For each connection the dient IP address is logged along with the time of the connection If there the user validation fails the cause of the failure is logged note the E in the second column denoting an error condition If the user validation succeeds the connection to the DBC Server is logged Appendix B 71 JDBC Monitor MPE iX Monitor Log MPE X Monitor Log This is a sample of a standard list spool file for the MPE iX HP J DBC The beginning of the file shows when the monitor was started JOB JDBCMON MGR JDBC JDBC Priority DS Inpri 8 Time UNLIMITED seconds Job number 3586 FRI OCT 9 1998 10 52 AM HP3000 Release C 50 00 User Version C 50 00 MPE iX HP31900 B 79 06 Copyright Hewlett Packard 1987 All rights reserved STREAMED BY STREAMER MANAGER SYS 807471 LDEV 10 STREAM DATE FRI OCT 9 1998 10 52 AM KKKKKKKKKKKKKKK SYSTI 39 KKKKKKKKKKKKKKK KKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKK This is a private system operated for Hewlett Packard company business Authorization from HP management is required to use this system Use by unauthorized persons is prohibited KEKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKK
14. sample client and a simple client Both can be used to test the installation of J DBC components on the client and server Thesimple dient is called SimpleClient and is a bare bones text based application that makes a connection to an ALLBASE SQL or IMAGE SQL database and allows the user to send SQL statements and retrieve the results There are no frills in the application so as to make the code as simple as possible This dient should mainly be used for educational purposes The rest of this discussion will focus on the more robust application The sample dient is called SampleClient and is a more robust version of the simple dient It shows how to use a dialog box to obtain user input and also better formats the result set output Thefirst step using the sample dient is to build it by compiling the J ava source code Normally you would dothis by using theJ ava compiler command the command you use may be different depending on your J DK javac SampleClient java If this step fails then you most likely do not have the HP Driver for J DBC dass files in your dass path Set your CLASSPATH environment variable or use the classpath J ava compiler option to include the directory in which the driver class files are located For more information refer to your DK documentation on including other class files ava compiler has compiled the source file the following J ava class file will be created SampleClient clas
15. servers The URL is also logged if tracing is turned on For this reason the second connection method is preferred 38 Chapter 4 Using the HP Driver for JDBC URL Syntax Without User Name and Password URL Syntax Without User Name and Password The second connection method specifies the user id and password as method arguments so this information is not present in the URL java sgql DriverManager getConnection url uid pwd where String url jdbc allbase host port database TRACE trace String uid uid String pwd pwd host Name or IP address of the sever host port Optional port number on which the DBC Monitor is listening If not specified the default part number 31700 is used database ALLBASE SQL or IMAGE SQL database name uid Server host userid that is authorized to access the database pwd Server host password that matches the user id provided above trace Optional trace values separated by the vertical bar character For more information on tracing see the Troubleshooting section Chapter 4 39 Using the HP Driver for JDBC HP UX Userid and Password HP UX Userid and Password For the DBC Monitor on HP UX the userid and password in the client URL connection string must correspond to a valid HP UX user name and password for the server host However the user accounts may be configured to not allow shell logons to prevent someone from using the user name and password
16. the process that is logging the information The second column shows the timestamp 80 Appendix C D Simple Client Source Code TheJ ava source listing given in this appendix is a program demonstrating the use of HP Driver for J DBC to access an ALLBASE SQL or IMAGE SQL database It can be run from any platform which has J DK 1 1 or above and HP Driver for J DBC installed This program is part of the HP Driver for DBC distribution and it is named SimpleClient java JDBC Simple Client This simple Java application loads a JDBC Driver prompts the user for connection information creates the Driver URL makes the connection to the database and sends user provided SQL statements to the database If the result of the statement is a result set it is printed Out This code and information is provided as is without warranty of any kind either expressed or implied import java io import java net URL import java sql class SimpleClient private static Connection null private static Statement stmt null Command java SimpleClient 7 public static void main String args 81 Simple Client Source Code User input fields String hostName getString Host Name String userName getString User Name String userPassword getString User Password String dbName getString Database Na
17. to actually log on to the server host For example a user with a HP UX user name of user1 and password pass1 would use the method call java sql DriverManager getConnection jdbc allbase host userl mydatabase userl pass1 40 Chapter 4 Using the HP Driver for JDBC MPE iX Userid and Password MPE iX Userid and Password The MPE iX operating system supports user names account names group names and session names There are also user passwords account passwords and group passwords Various combinations of these names and passwords are supported by the MPE iX J DBC Monitor for user validation The format for the MPE iX userid is sessionname username accountname groupname The group and session names are optional The format for the MPE ix password is userpass accountpass grouppass If a group name was specified in the userid and there is a group password the group password must also be specified in the password parameter In a simple example a user with an MPE iX user name of user1 and an account name of acct 1 with the respective passwords of upass1 and apass1 would use the method call java sql DriverManager getConnection jdbc allbase host dbname userl acctl upassl apass1 In amore complicated example a user with an MPE iX user name of 1 account name of acct1 group name of group1 session name of sess1 with the respective passwords o
18. 000 Hanover Street Palo Alto CA 94304 U S A Copyright 1999 by H ewlett Packard Company Contents 1 Introduction J DB Garcia ee ete ee ee a ee tk 12 HPJDBC Components cs cis Shae Pe eee Pe a eet ee EE Pee E Tx 13 2 Requirements J ava 16 HP UX Server 5 ete ees 17 Server lt 18 ALLBASE SQL or IMAGE SQL lt 19 3 Installation HP UX Server 5 22 Server Components 23 J ava Client Components aci ienaa en aa e 24 Downloading the HP DBC 24 Using ETP ARPA SYS 6 chs ht Bets Oe on Pe aig Re xn d 24 Using Reflection 5 25 Extracting the HP Driver for J DBC Class 25 Example the Win32 Platform
19. HP Driver for J DBC User s Manual HP 3000 MPE iX Computer Systems Edition 1 D HEWLETT PACKARD Manufacturing Part Number 36216 90217 E 0399 U S A March 1999 Notice The information contained in this document is subject to change without notice Hewlett Packard makes no warranty of any kind with regard to this material including but not limited to the implied warranties of merchantability or fitness for a particular purpose Hewlett Packard shall not be liablefor errors contained herein or for direct indirect special incidental or consequential damages in connection with the furnishing or use of this material Hewlett Packard assumes no responsibility for the use or reliability of its software on equipment that is not furnished by Hewlett Packard This document contains proprietary information which is protected by copyright All rights reserved Reproduction adaptation or translation without prior written permission is prohibited except as allowed under the copyright laws Restricted Rights Legend Use duplication or disclosure by the U S Government is subject to restrictions as set forth in subparagraph 1 ii of the Rights in Technical Data and Computer Software clause at DFARS 252 227 7013 Rights for non DOD U S Government Departments and Agencies are as set forth in FAR 52 227 19 c 1 2 Acknowledgments J ava is a U S trademark of Sun Microsystems I nc Hewlett Packard Company 3
20. HP UX user id For the MPE iX platform this is composed of the group and account passwords for the group and account provided as the user name The user password is masked by theJ DBC Driver before it is transferred over the network to prevent casual detection Database Name This is the name of the ALLBASE SQL or IMAGE SQL database file on the server On HP UX this is the fully qualified path and filename of the database file On MPE ix this is either the fully qualified three part filename of the database or the database name if a user account group name was specified Database name for usually includes the location of the DBE If the username does not include the same logon group the full location must be provided These values must be validated before a connection is madeto the J DBC Server The sample dient provided with the HP Driver for J DBC can be used to test the correctness of these values 36 Chapter 4 Using the HP Driver for JDBC Connection Parameters Once these values have been determined the DBC Driver connection URL can be synthesized There are two primary DBC Driver class methods that can be used to connect to the database The first one requires that all connection parameters be provided in a J ava String URL including the user name and password However this information can be logged on the client thus recording the user name and password Remember that the user name and passwords can be
21. KKKKKKKKKKKKKKKKKKKKKKKKKKKK WARNING This computer is accessed by companies outside HP 5 All security and control procedures must be strictly followed Ck CK Ck Ck SK CI Ck CK CC CK C Ck KC Ck KC C KC CK CC KC Ck Ck CK CK kk S Ck kk S A x KK Kk kx Kk Mk ko ko END OF PROGRAM run jdbcmon jdbc sys info 31700 pri cs Waiting for a client connection Connection established from 15 0 121 172 on FRI OCT 9 1998 10 54 AM Unable to validate ID mgr jdbc AIFCHANGELOGON rc 2520 Invalid account 72 Appendix B JDBC Monitor MPE iX Monitor Log password specified Waiting for a client connection Connection established from 15 0 121 172 on FRI OCT 9 1998 10 55 AM Unable to validate ID mgr jdbc AIFCHANGELOGON rc 2521 Invalid user pas sword specified Waiting for a client connection Connection established from 15 0 121 172 on FRI OCT 9 1998 11 00 AM Server servprog info 2 created for userid mgr jdbc PIN 1914 Waiting for a client connection Connection established from 15 0 121 172 on FRI OCT 09 1998 11 09 AM Monitor shutdown requested on FRI OCT 9 1998 11 09 AM Waiting for 2 children to terminate Waiting for 2 children to terminate Waiting for 1 child to terminate JDBC Monitor Ending on FRI OCT 9 1998 11 12 AM END OF PROGRAM 3 lapsed 20 FRI 9 1998 11 12
22. Lists Java Ghent Files rere a E E E t enar t t 90 HP Ux Server Files J esee ea m Ra E e e EUR ROO 92 MPE IX Server EMeS su TER ease Ree aw er c rb ale Ae rp eue rte TEES 93 Index Figures Figure 1 1 J DBC Client Server Application Figures Tables Table 5 1 Data Type Mapping Table 5 2 Data Type Conversions Table 5 3 Unsupported Statements Tables Preface J ava Database Connectivity J DBC is a standard Application Programming Interface for database access from J ava HP Driver for J DBC is an implementation of the standard J DBC API It consists of Hewlett Paclard s fully java Driver for DBC network protocol and database server interface components for concurrent access to IMAGE SQL and ALLBASE SQL databases This manual provides information about installing configuring and using HP Driver and server components for J DBC Chapter 1 Introduction presents a general summary of J ava Database Connectivity and HP Driver for J DBC components Chapter 2 Requirements provides information about J ava MPE HP UX ALLBASE SQL and IMAGE SQL versions required to use HP Driver for DBC Chapter Installation describes the installation steps Chapter 4 Using the HP Driver for J DBC shows how to use HP Driver for J DBC from ava programs Chapter 5 ALLBASE SQL Specifics provides information specific to ALLBASE SQL and HP Drive
23. P Driver for DBC dass methods that are called by both the application and the driver itself TIME Include the time in HHMMSSFFF format on all tracing output lines where HH is the hour from 00 to 23 MMisthe minute from to 59 SS is the second from 00 to 59 and FFF is the millisecond from 000 to 999 The information appears in the third column of the tracing output DATE Include the date in yyYYyMmMpDD format on all tracing output lines where is the year from 0000 to 9999 is the month from 01 to 12 is the day of the month from 00 to 31 This information appears in the second column of the tracing output 56 Chapter 6 ESTAMP JECT TH READ CONN WA SQ AL RI Ei EN EBUG ECTION RNI NETWORK ROR NG Troubleshooting Client Tracing Include both the date and the time on all tracing output lines This is just a combination of the DATE and TIME tracing values Indude the object hash code in the format HHHHHHHH On all tracing output lines where is the eight digit hexadecimal value of the hash code This information appears in the first column of the tracing output Indude the executing thread name on all tracing output lines This information appears as the fourth column of the tracing output Trace debugging statements from the driver Th
24. UBLE PRECISION DECIMAL NUMERIC CHAR VARCHAR DATE TIME DATETIME INTERVAL 44 Chapter 5 ALLBASE SQL Specifics ALLBASE SQL to JDBC Data Type Conversions ALLBASE SQL to J DBC Data Type Conversions Table 5 2 shows the supported data type conversions between ALLBASE SQL J DBC For conversions from J DBC ava please refer to aJ DBC book or the J ava J DBC documentation Those conversions are generic to all J DBC Drivers A description of each ALLBASE SQL and J DBC data type can be found in Appendix A Data Types Suggested conversions are denoted by a capital letter X in the conversion grid Conversions which are supported but which may result in a loss of precision overflow or rounding are denoted by a lowercase letter x in the conversion grid Table 5 2 Data Type Conversions J DBC DATA 5 T TYPES vertical MIN A N A I TI IG A OJU C M T M M ALLBASE SQL R C IG A LIGIN 5 horizontal NI E T E JA A A Y NA T 18 LIC RIR T R B M H Y R A R Y SMALLINT 16 bits x X X X X 1X X X X X X x x INTEGER 32 bits x x X X X xX X X x x X X X X X X X X X x FLOAT 1
25. client archive files packaged in three formats and set up theJ DBC monitor startup scripts Once the installation is complete you may delete the tar file and the installation script For a complete list of the files that are installed please refer to Appendix E HP ALLBASE SQL J DBC File Lists If existing files are found during the installation they are backed up by renaming them with a 1 dot one suffix any existing file with the 1 suffix is overwritten This allows the installer to manually revert tothe previous installation if desired 22 Chapter 3 Installation MPE iX Server Components MPE iX Server Components The installation of the HP J DBC product must be done by a system administrator on the server host where the database resides The components of HP J DBC are distributed as an archive file HFSFILES JDBC SYS Stream the DBC installation job T00IJDBC to perform the actual installation stream IOOIJDBC JDBC SYS Theinstallation script will extract the server files and dient archive files and set up theJ DBC monitor startup scripts For a complete list of the files that are installed please refer to Appendix E HP ALLBASE SQL J DBCFileLists Chapter 3 23 Installation Java Client Components J ava Client Components The HP J DBC product must first be installed on the server host before the client can be installed This is because the client files ar
26. ct The first configuration option LOGFILE refers to the path and filename for the HP J DBC Server log file On HP UX this is normally set to opt allbase jdbc logs servlog On this is normally set to SERVLOG JDBC SYS The second configuration option TIMEOUT specifies the number of seconds that the HP J DBC Server can remain idle before terminating The next set of lines are the LOGGING levels used to determine the type of information to be logged to the server log file The meanings of the various logging levels are discussed in the Troubleshooting Section under Server Logging of the HP Driver for J DBC User s Manual Chapter 3 27 Installation Configuring HP JDBC Server The server configuration file is read in anew each time a new server process is started Thus once the file is edited and saved the changes will take place beginning with the next server process If logging is turned on in this file it will bein effect for all subsequent server processes For a complete listings of Server log file please refer to Appendix C HP DBC Server 28 Chapter 3 Installation Starting and Stopping HP JDBC Monitor Starting and Stopping HP J DBC Monitor HP J DBC Monitor is the server side counterpart of HP Driver for J DBC present on the client side For DBC application to connect toa database the DBC Monitor must be running on the database server J DBC Mo
27. e default server configuration file is servcfg and it is located in opt allbase jdbc bin On the name of the default server configuration file is SERVCFG and it is located in JDBC SYS LOGFILE opt allbase jdbc logs servlog TIMEOUT 7200 LOGGING ERROR LOGGING CONNECTION LOGGING INFO LOGGING WARNING LOGGING FATAL LOGGIN LOGGIN LOGGIN LOGGIN G G G G G LOGGING IN G G G G G LOGGIN LOGGING NONE If LOGGING NONE is not commented out it must be the last line for it to have the desired effect The first configuration option LOGFILE refers to the path and filename for the HP J DBC Server log file On HP UX this is normally set to opt allbase jdbc logs servlog On this is normally set to SERVLOG JDBC SYS The second configuration option TIMEOUT specifies the number of seconds that the HP J DBC Server can remain idle before terminating This idle time is the time between the returning of a result to the client and the submission of the next client request It does not indude database processing time If this valueis not provided or is set to 0 the timeout will be set to an infinite value 78 Appendix HP JDBC Server HP JDBC Server Configuration File The next set of lines are the LOGGING levels used to determine the type of information to be logged to the server log file The meanings of the
28. e bundled with the server product The HP Driver for J components consist of the Driver J ava dass files and a sampleJ DBC client source file These are the only components required on the client side The driver components are packaged in three formats the same files contents are each package UNIX tape archive TAR J ava archive J AR and Windows ZIP ZIP Setting up HP Driver for J DBC client involves Downloading the appropriate HP J DBC archive file to a temporary directory Extracting the HP J DBC archive file onto your client platform Downloading the HP J DBC Archive File On an HP UX server the files are located in opt allbase jdbc driver with the following names driver jar Driver files in J ava archive package driver tar Driver files in UNIX tape archive package driver zip Driver filesin a Windows ZIP package On an server the files are located in JDBC Sys with the following names DRIVERJ Driver files in J ava archive package DRIVERT Driver files in UNIX tape archive package DRIVERZ Driver files in a Windows ZIP package Using FTP ARPA SYS For the HP 3000 server use MANAGER SYS JDBC as the username From your client platform ftp mpe server gt binary gt get DRIVERT Of DRIVERJ Of DRIVERZ 24 Chapier 3 Installation Java Client Components For the HP 9000 server use any login as the username
29. encing dropped connections For a complete listings of J DBC Monitor log file please refer to Appendix B J DBC Monitor 30 Chapter 3 Using the HP Driver for J DBC This chapter describes how to use HP Driver for DBC from J ava Programs Information about setting up environment variables compiling J ava programs loading the HP Driver for DBC and connecting to a database is provided 31 Using the HP Driver for JDBC Java Class Path J ava Class Path Before the HP Driver for DBC can be used ava class path must be set to indude the HP Driver for J DBC dass files Without this setting both the ava compiler and the ava Run Time Environment will not be abletolocate the HP Driver for J DBC Please refer to your J DK documentation for more explicit details as to how to set the ava dass path In general the ava class path must include the directory in which the HP Driver for DBC class files have been installed If the HP Driver for J DBC class files are installed in opt java 1lib com hp jdbc allbase then the ava dass path must include the directory opt java lib In most cases this would mean that the CLASSPATH environment variable must be set to something resembling Example on the HP 9000 CLASSPATH opt java lib 32 Chapter 4 Using the HP Driver for JDBC Sample JDBC Client Sample J DBC Client The HP Driver for comes with the source code to two DBC applications a
30. er Files 94 Appendix E Index A ABORT OB command 30 acceptable SQL 48 ALLBASE SQL 13 ALLBASE SQL 36 ALLBASE SQL database 12 applet 13 archive file 24 child processes 29 class files 32 CLASSPATH 32 33 classpath 33 CLASSPATH environment 33 Client Components J ava 24 client components J ava class files 24 sample source file 24 client files 24 client tracing 56 client server application 12 client side tracing 34 common problems connection process 54 components 16 configuration option LOGFILE 27 TIMEOUT 27 Connection Parameters 36 connection parameters 38 connection process 54 connection process common problems 54 conversion grid 45 D data type conversions 45 data type mapping 44 database name 36 databases 13 DBMS independent protocol 12 decimal value 46 default port number 36 default port number 31700 29 default server configuration file 27 downloading archive file 24 downloading HP J DBC archive file 24 Index driver components J ava archive J AR 24 UNIX tape archive TAR 24 Windows ZIP ZIP 24 Driver files J ava archive package 24 UNIX tape archive package 24 Windows ZIP package 24 Driver Manager 35 DRIVER 24 DRIVERT 24 DRIVERZ 24 dropped connections 30 dynamic parameter 51 ENVIRONMENT VARIABLE 32 extract contents 25 extracting driver class files 25 Win32 Platform 25 extracting file 24 F floating point va
31. f upass1 apass1 and gpass1 would use the method call java sql DriverManager getConnection jdbc allbase host dbname sessl userl acctl groupl upassl apassl gpass1 Chapter 4 41 Using the HP Driver for JDBC MPE iX Userid and Password 42 Chapter 4 ALLBASE SQL Specifics This chapter describes details specific to HP Driver for J DBC and ALLBASE SQL Data type mapping data type conversions unsupported data types unsupported SQL statements and support for stored procedures are discussed 43 Table 5 1 ALLBASE SQL Specifics ALLBASE SQL to JDBC Data Type Mapping ALLBASE SQL to DBC Data Type Mapping Table 5 1 shows what the HP Driver for J DBC will report as the DBC data type for each ALLBASE SQL data type These are the java sql Types values that will be returned from the java sql ResultSetMetaData getColumnType method An X in the column indicates the data type mapping A description of each ALLBASE SQL and J DBC data type can be found in Appendix A Data Types Data Type Mapping JDBC DATA TYPES vertical ALLBASE SQL DATA TYPES horizontal SMALLINT 16 bits INTEGER 32 bits REAL FLOAT 1 24 FLOAT 25 53 R 2 L 4 2 Az rr zu zmaom4Az 412 0 4A gt orn gt O xmzzcz 4 A PIOmX P APIOxP ozor lt 2 2 0 450 2 0202 P umsz d4 DO
32. he name monlog old and the log file reset to zero length This prevents the log file from growing without bounds The maximum amount of disk space that will be used for the monitor log is twice the amount specified by this option total size of the log file and the backup log file Thethird configuration option MAXSESSIONS sets the limit on the maximum number of concurrent sessions that is allowed by the monitor Limiting the number of active connections can increase performance of the server host and prevent overload of the system Client connections are rejected with java sql SQLException if the number of sessions are exceeded It means that the server is busy and that they should try again later If this number is not set or set to 0 then the number of sessions is unlimited Thelast twolines of the configuration file above provide the mapping to the J DBC Server executable The service name of the J DBC Server is JDBCSERV JDBC SYS The SERVICE line after the service name gives the full path and filename of the DBC Server 68 Appendix B JDBC Monitor HP UX Monitor Configuration File Normally there is no reason to edit the monitor configuration file If for some reason it is edited the monitor must be reset using the monct r1 command to trigger the monitor to re read the configuration file The syntax for this command is monctrl reset portnumber where portnumber 15 the p
33. ified Database If this step fails the client should receive an ALLBASE SQL error message through the java sq1 SQLException that is shown Common causes of this failing are incorrect database name or a corrupt database M ake sure the case sensitivity of the database name is also checked If all the preceding steps are successful the Driver Manager will return a connection to the database Chapter 6 55 Troubleshooting Client Tracing Client Tracing Tracing of the DBC Driver client class files is invoked by adding tracing commands to the connection URL The connection URL can be altered to both invoke tracing and to control the type of information that is traced The tracing information is sent to a J ava stream which must be specified by the application using the java sql DriverManager setLogStream method Both the tracing level and tracing output must be specified before any tracing can be done Note that the use of tracing will impact performance of the application The greater the detail in the tracing the slower the performance of the application The URL syntax to invoke tracing is jdbc allbase server port database TRACE trace where trace is any vertical bar separated combination of the values ARGUMENTS Trace HP Driver for J DBC dass method arguments nput arguments and return values are all traced Only the methods called by the application are traced ARGUMENTS ALL Trace all H
34. ion file logs monlog J DBC Monitor log file logs servlog J DBC Server default log file driver driver tar J DBC Driver filesin UNIX tapearchive format driver driver jar J DBC Driver files in J ava archive format driver driver zip J DBC Driver files in Windows ZIP format Startup scripts sbin init d Jdbcmonitor J DBC Monitor startup shutdown script etc rc config d Jdbcmonitor J DBC Monitor startup shutdown script configuration variables sbin rcl d K910Jdbcmonitor System shutdown link to the startup shutdown script sbin rc2 d S090Jdbcmonitor System startup link to the startup shutdown script 92 Appendix E HP ALLBASE SQL JDBC File Lists Server Files MPE iX Server Files In JDBC SYS group JDBCMON JDBCSTOP JSTRTMON JSTOPMON JDBCSERV SERVCFG SERVLOG DRIVERT DRIVERJ HFSFILES I001JDBC Appendix E J DBC Monitor startup executable program J DBC Monitor shutdown executable program J DBC Monitor startup job stream J DBC Monitor shutdown job stream J DBC Server executable program J DBC Server text configuration file J DBC Server default log file J DBC Driver files in UNIX tape archive format J DBC Driver files in J ava archive format J DBC Driver files in Windows ZIP format J DBC Distribution archive J DBC Installation job J DBC README file 93 HP ALLBASE SQL JDBC File Lists MPE iX Serv
35. is will produce a large amount of output so it should be used sparingly Trace driver connection information Trace driver network information Trace driver errors Trace driver warnings Trace SQL statements that are passed tothe driver Trace everything For example the URL to turn on tracing of all connections with time stamp information would be jdbc allbase server database TRACE CONNECTION TIMESTAMP Do not use any space characters between tracing levels and the vertical bar separator as some browsers and web servers do not allow space characters in a URL Remember that the application must also set the tracing stream using the java sql DriverManager setLogStream method Chapter 6 57 Troubleshooting Client Tracing Here is a sample output of a J DBC trace log using the trace flag ALL 0f457ce2 19980917 110116 300 main JdbcConnection createStatement void 0 457ce2 19980917 110116 312 main JdbcConnection createStatement JdbcStatement 0f458ef4 0 458df3 19980917 110116 903 main JdbcResultSet new SolidResultSet 0f458e9b 0 458df3 19980917 110116 907 main JdbcResults new JdbcResultSetG0f458df3 0 458df3 19980917 110117 088 main JdbcResultSet getMetaData void 0 458df3 19980917 110117 152 main JdbcResultSet getMetaData JdbcResultSetMetaDataQ0f458c9f 0 458df3 19980917 110117 164 main JdbcResu
36. ltSet next void 0 458df3 19980917 110117 167 main JdbcResultSet next tru 0 458df3 19980917 110117 170 main JdbcResultSet getString 1 0f458df3 19980917 110117 199 main JdbcResultSet getString 33 0 458df3 19980917 110117 202 main JdbcResultSet wasNull void 0 458df3 19980917 110117 205 main JdbcResultSet wasNull false 0 458df3 19980917 110117 212 main JdbcResultSet next void 0 458df3 19980917 110117 215 main JdbcResultSet next fals 0 458df3 19980917 110117 218 main JdbcResultSet close void 0 458df3 19980917 110117 221 main JdbcResultSet close void 0 457ce2 19980917 110118 087 main JdbcConnection close void 0 457ce2 19980917 110118 090 main JdbcConnection close Closing JdbcConnection object Disconnecting all connections Of457ce2 19980917 110118 093 main JdbcConnection close Closing connection number 0 0f457ce2 19980917 110118 099 main JdbcConnection close Of457ce2 19980917 110118 JdbcConnection object closed 58 102 main JdbcConnection close void Chapter 6 Troubleshooting Client Tracing The first column shows the object hash code Note that the object hash code can be matched with the objects returned by various methods The second column shows the date September 17 1998 The third column shows the time 11 01 AM The fourth column shows the thread name in this case there is only the main thread The last column contains the tracing information In each line
37. lue 46 H host name 36 HP Driver for DBC 12 13 HP Driver for J DBC components 24 HP J DBC Client components 16 HP J DBC Driver 32 HP J DBC Monitor 27 HP J DBC Server components 27 HP UX server components 22 IMAGE SQL 36 IMAGE SQL database 12 INSERT statement 50 installation script 22 23 integer value 46 address 36 J JAR 24 jar command 25 J AR package 25 J ava API 11 J ava application 12 J ava class path 32 95 Index J ava Client Components 24 J ava compiler 32 33 J ava compiler option classpath 33 J ava components 16 J ava dialog box 34 J ava Requirements 16 J ava Run Time Environment 32 J ava SDK 12 33 J ava String URL 37 J ava Virtual Machine 33 J ava Virtual Machines 12 ava VM rules 46 J AVA iX 26 J DBC Driver Manager 12 J ava application 12 J DBC API 12 13 J DBC client server application 12 J DBC Driver 13 J DBC Driver Manager 12 35 J DBC Monitor 13 29 shutdown 29 startup 29 J DBC Server 13 DBC server components 22 J DBC SQL 13 J DBC trace log 58 DK 16 33 J DK components 16 J STOPMON stream job 30 J STRTMON stream job 30 K kill command 29 killing the monitor 29 L loading HP J DBC Driver 35 log file 27 LOGFILE 27 logging levels 27 M main thread 59 marker character 50 method arguments 39 monctrl command 29 monctrl tool 29 MPE X 26 MPE iX HP DBC Monitor 30 96 MPE iX Mo
38. me Create the URL based on the user input String url jdbc allbase hostName dbName 2 Try to make the connection to the database try Load the jdbc driver Class forName com hp jdbc allbase JdbcDriver Attempt to get a connection con DriverManager getConnection url userName userPassword catch SQLException ex System out printin SQLException caught during connection printException ex System exit 0 catch Exception e System out printin Exception caught during connection 82 Appendix D Simple Client Source Code e printStackTrace System exit 0 Main loop String sql null ResultSet rs null while true Ask user for an sql statement System out printlin sql getString SQL gt System out printin If empty string then exit if sql null sql length 0 break Submit the sql statement try stmt con createStatement If execute returns true there is a result set if stmt execute sql rs stmt getResultSet displayResultSet rs Otherwise there is an update count else System out println stmt getUpdateCount rows changed Appendix D 83 Simple Client Source Code catch SQLException ex printException ex
39. monitor when the host machine is still up and running the tool monctr1 also installed with the HP J DBC product can be used to perform the startup and shut down The command to use monct r1 iS monctrl start reset kill portnumber The start argument will start up the monitor on either the default port number or on the specified port number The kill argument will first terminate each of the monitor s active child processes and then terminate the monitor process itself Doing this gives a better chance of being able to restart the monitor once it has been shut down The portnumber argument is optional if it is not specified the default port number will be used monctri should only be executed by root because the monitor must run as root to allow it to spawn processes and set user ids If it is not started by root other users will not be able to connect to the monitor The reset argument is discussed in the section dealing with the monitor configuration file You cannot start a monitor on the same port number as a currently running monitor You also can not restart a monitor on its original port number until all child processes that were spawned by the previous monitor process are terminated and the port released If the monitor is intentionally terminated or accidentally terminates all of its child processes must be terminated before it can be restarted For this reason the monitor must not be terminated by using
40. ng connections on the default or other port number Step 5 Validating the Userid and Password e If this step fails the most likely cause is an incorrect userid or invalid passwords for the userid You can attempt to use ftp or telnet to verify that the userid and passwords are valid Step 6 Spawning a Server Process by the J DBC Monitor e If this step fails some resource limit is reached on the server the monitor logs should indicate the reason for the failure 54 Chapter 6 Troubleshooting Connection Process Step 7 Switching to the Effective User Once the process is spawned the monitor switches the effecti ve user id to the user that was supplied in the user validation This ensures that the user only has the privileges assigned to the user If this step fails the most likely cause is that the monitor was not started by root the superuser for HP UX or a user with the PM capabilities for MPE iX The user can log on to the server and verify that the monitor process was started by the correct user and has the proper capabilities Step 8 Executing the J DBC Server by the Spawned Process If this process fails the DBC Server executable could be corrupt have incorrect permissions be missing or be incompatible to the server operating system installed components for incorrect platform The user can log on to the server and manually verify the server installation files Step 9 Attempting to Connect to the Spec
41. nitor can be started manually or automatically at system startup by including the DBC Monitor start commands in the system startup scripts HP UX Monitor Startup and Shutdown The startup and shutdown of the HP UX J DBC Monitor is normally controlled by the system startup and shutdown scripts Thus as long as the machine is up and running the DBC Monitor will also be up The only time the Monitor should ever be brought down is to install a newer version In this case use the monct r1 command to kill the Monitor process Do not usethe UNIX xi11 command as this could render the server unable to start a new Monitor process The monct r1 command to shut down the monitor is monctrl kill portnumber The portnumber parameter is necessary only if the monitor you wish to shut down is not running on the default port number 31700 Torestart the monitor after it has been accidentally terminated or was shut down use the monct r1 command to start it up monctrl start portnumber Again the portnumber parameter is necessary only if you wish to start the monitor on a port number other than the default port number 31700 You cannot start a monitor on the same port number as a currently running monitor You also can not re start a monitor on its original port number until all child processes that were spawned by the previous monitor process areterminated and the port released If the monitor is intentionally terminated or accidentally termina
42. nitor shutdown 30 MPE iX Monitor startup 30 MPE iX Server Components 23 MPE iX userid format 41 N numeric casting 46 object hash code 59 parameterized SQL statments 50 password 40 password parameter 41 platforms HP UX 27 27 port number 36 portnumber parameter 29 preventing casual detection 36 Procedures 51 R Reflection Software 25 renaming files 22 requirements 16 S sample dient 33 SELECT statement 50 servcfg 27 Server Components MPE iX 23 server components 22 server configuration file 60 server host 36 server log file 27 Server Logging 27 server logging 60 server logging levels 60 Server Requirements ALLBASE SQL 19 HP UX 17 IMAGE SQL 19 MPE iX 18 shell logons 40 shutdown 29 simple dient 33 SimpleClient 33 Index Index space characters 57 Starting HP J DBC Monitor 29 startup 29 startup command 36 startup script 23 startup scripts 22 Stopping HP J DBC Monitor 29 stored procedures 51 stream jobs 30 J STOPMON 30 J STRTMON 30 stream script 23 suggested conversions 45 Sun compliant J DK version 1 1 16 t command line switch 34 TAR 24 tar command 25 tar file 22 TAR package 25 temporary directory 22 thread name 59 TIMEOUT 27 trace flag ALL 58 trace flags 34 trace output filename 34 tracing 56 tracing information 59 tracing URL syntax 56 U UNIX kill command 29 UNIX
43. nterfaces and provide an implementation of DBC driver that can communicate with an ALLBASE SQL or IMAGE SQL database The HP Driver for J DBC typically will reside on the dient side of the user application It provides the translation from the ava language and theJ DBC API tothe HP proprietary network protocol TheJ DBC Monitor is a component that is installed on theJ DBC server host that manages all dient J Driver connections to the server host It is typically started as a daemon when the server machine is booted All J DBC dient connections are made through the J DBC Monitor The monitor performs validation of the userid and password that are passed in the client connection request and spawns J DBC Server processes to serve each of the client connections Once the server process is spawned the monitor returns to wait for the next client connection TheJ DBC Server is the server process that is spawned by the DBC Monitor to service a dient connection It handles the translation from the HP proprietary network protocol tothe ALLBASE SQL calls There is at least DBC Server process for each client connection to the server host More than DBC Server process may be used to handle multiple client statements using the same connection This component also handles the translation from J SQL to ALLBASE SQL and conversion of the database data from ALLBASE SQL format to J DBC format Both the DBC Monitor and the DBC Se
44. oating point number that supports 15 digits of mantissa Double precision floating point number that supports 15 digits of mantissa Small fixed length binary value Small variable length binary value Large variable length binary value A value representing a date consisting of day month and year A value representing a time consisting of hours minutes and seconds A DATE plus a TIME plus a nanosecond field Appendix A J DBC Monitor This appendix describes one of the HP J DBC Server components the J DBC Monitor J Monitor configuration logging startup and shutdown are discussed 65 JDBC Monitor HP UX Monitor HP UX Monitor On the HP UX platform a separate server process services each client connection These processes are dissociated from the monitor process so if the monitor terminates or dies the existing server processes servicing client requests will continue to function until the client closes the connection or the server times out Normally the monitor is automatically started up during the host boot process via a startup script This startup script is installed when the HP J DBC product is installed on the server Normally the monitor is never brought down unless the machine is brought down in which case it will be automatically stopped via a stop script that is installed along with the startup script If there is a need to start or shut down the
45. ort number on which the monitor to be reset is running If the port number is not specified the default port number is assumed The reset of the monitor occurs on the fly without affecting any of the current established dient connections Appendix B 69 JDBC Monitor HP UX Monitor Log HP UX Monitor Log This is a Sample of an HP UX J DBC monitor log file The beginning of the file shows when the monitor was started as well as the monitor configuration settings The left hand column shows the UNIX process id number PID of the process that is logging the information 27152 27152 JDBC Monitor starting up at Wed Oct 7 09 25 00 1998 27152 Configuration file moncfg 27152 Socket has port 31700 27152 JDBC Monitor PID 27152 27152 ax logfile size bytes 100000 27152 Max number of sessions 128 27152 alias set JDBCSERV JDBC SYS opt allbase jdbc bin jdbcserv 0 PUTENVs 27152 27152 15 0 121 172 Wed Oct 7 09 25 47 1998 27426 PID 27426 assigned 27426 Login successful user subo 27426 Service JDBCSERV JDBC SYS 27426 atched to JDBCSERV JDBC SYS 27426 Service to exec opt allbase jdbc bin jdbcserv 27152 PID 27426 died 0 left 27152 15 0 121 172 Wed Oct 7 09 26 20 1998 27545 PID 27545 assigned 27545 E Invalid userid user joebobl 27545 Exiting 27152 PID 27545 died 0 left 27152 15 0 121 172 Wed Oct 7 09 26 39 1998 27578 PID 27578 assigned 27578 E Invalid
46. ow to overcome them Ways to enable client tracing and server logging to identify problems are also given 53 Troubleshooting Connection Process Connection Process Most problems arise during the initial connection of the DBC client to the database For this reason we shall first completely describe the steps that occur during the connection process and mention some common problems that can arise Step 1 Loading the HP Driver for J DBC Class Files e fthis step fails the most likely cause is that the ava CLASSPATH environment variable has not been set correctly Other possible causes indude the driver files not being installed or unreadable or corrupt driver files Step 2 Loading the Network Components e If this step fails it could mean a bad network connection or the dient not being connected to the network Step 3 Loading the Specified Server Name e If this step fails the most likely cause is either an incorrect or invalid server name an unreachable server network down or outside firewall or a down server The user can try to use other methods to reach the server such as ping telnet or ftp Step 4 Connecting to the 31700 or the Specified Port Number fthis step fails it means that there either was no process listening on that port number or something other than the DBC Monitor was listening on that port number A server administrator can verify that theJ DBC Monitor process is running and accepti
47. ports the following types of ALLBASE SQL stored procedures through the java sql CallableStatement interface 1 Procedures that return one or more result sets 2 Procedures that take one or more input arguments 3 Procedures that return one or more output arguments 4 Procedures that havea return status value Procedures may be of one or more of these combinations for example the procedure may have parameterized input and a return status and return one or more result sets Stored procedures are executed by first creating a java sql CallableStatement object with the execute procedure SQL statement The execute procedure SQL statement in one or more of the following formats each format corresponds to one of the cases listed above 1 EXECUTE PROCEDURE PROCNAME P P 2 EXECUTE PROCEDURE PROCNAME 10 P P 3 EXECUTE PROCEDURE PROCNAME OUTPUT OUTPUT ONLY 4 EXECUTE PROCEDURE PROCNAME Input arguments can be either explicitly specified in the SQL statement or dynamic parameters can be used for passing values when the statement is executed Output only arguments must be specified with a dynamic parameter otherwise the output value will not be returned Chapter 5 51 ALLBASE SQL Specifics Stored Procedures 52 Chapter 5 Troubleshooting This chapter lists most frequently encountered problems in using HP Driver for DBC and h
48. r for J DBC Chapter 6 Troubleshooting discusses common problems encountered and their solutions Appendix A Data Types describes ALLBASE SQL amp J DBC data types Appendix B DBC Monitor contains information about J DBC Monitor for HP UX and MPE ix Appendix C HP J DBC Server presents a detailed description of HP DBC Server Appendix D Simple Client Source Code contains the J ava source code for a demonstration application Appendix E HP ALLBASE SQL J DBC File Lists identifies all the files that are part of this produc 10 Introduction J ava Database Connectivity DBC is ava API that enables development of J ava applications and applets with a wide range of relational databases It consists of a set of classes and interfaces written in J ava J DBC allows developers to write database applications using a standard ava API 11 Figure 1 1 Introduction JDBC JDBC A typical J DBC J ava application consists of a J ava application or applet the DBC Driver Manager a vendor specific DBC driver and a database The DBC Driver Manager is provided with the J ava SDK and its primary function is to load and register the vendor specific J DBC driver with the ava applications and then get out of the way The following diagram shows the various components of a client server J DBC application J DBC Client Server Application J ava Application
49. rver must be installed on the same host where the ALLBASE SQL or IMAGE SQL databases resi de Chapter 1 13 Introduction HP JDBC Components 14 Chapter 1 Requirements HP Driver for J can be used to access ALLBASE SQL or IMAGE SQL from ava Clients running under various operating systems This chapter specifies ava Client Database Server and Operating System requirements for successfully installing and running HP Driver for DBC 15 Requirements Java Requirements J ava Requirements The HP DBC Client components theJ DBC driver itself requires a Sun compliant J DK version 1 1 and above which includes DBC version 1 2 Install theJ DK from Sun or from your platform vendor Individual platform vendors may have their own requirements for the platform host For example J ava only runs on HP UX 10 20 and MPE iX 6 0 and above HP UX 9 x and MPE iX 5 5 are not supported J ava DK components only need to be installed on the dient platform The DBC server platforms use native components and must beinstalled on the same host wherethe ALLBASE SQL or IMAGE SQL databases reside 16 Chapter 2 Requirements HP UX Server Requirements HP UX Server Requirements The HP J DBC Server components require HP UX version 10 20 or greater H P U X components are required only if your DBC Server platform is the HP UX operating system Chapter 2 17 Requirements MPE iX Server Requirements MPE iX Server Req
50. s Torun the sample client follow the instructions provided with your J ava SDK for your client platform Make sure that your J ava CLASSPATH includes the directory in which the HP Driver for J DBC dass files have been installed so that the ava Virtual Machine is able to load them For most platforms the command to run the sample dient is java SampleClient w t Follow the prompts given by the application to connect to your database and to execute SQL statements The sample dient can be used as a starting point for developing your own applications or for troubleshooting connection problems with the database Chapter 4 33 Using the HP Driver for JDBC Sample JDBC Client The w command line switch will cause the sample client to display a J ava dialog box to accept user input of the connection information Normally the sample dient uses text mode input which displays the user password on the screen The t command line switch will cause the application to turn on client side tracing and prompt the user for the trace flags and the trace output filename 34 Chapter 4 Using the HP Driver for JDBC Loading the HP Driver for JDBC Loading the HP Driver for J DBC TheJ ava method that is used to load all DBC drivers is the class forName method To load the HP Driver for DBC the code is class forName com hp jdbc allbase JdbcDriver This loads the HP Driver for J DBC and registers it with the DBC Dri
51. s in the range of 1 to 3996 bytes Each byte stores 2 hexadecimal digits Binary string of variable length up to length n wheren is in the range of 1 to 3996 bytes Each byte stores 2 hexadecimal digits Binary string of fixed length up to length n wheren is in the range of 1 to 231 1 bytes This data typeis currently not supported by the HP Driver for J DBC Binary string of variable length up to length n wheren is in the range of 1 to 231 1 bytes This data typeis currently not supported by the HP Driver for J DBC 63 Data Types JDBC Data Types J DBC Data Types CHAR VARCHAR LONGVARCHAR NUMERIC SMALLINT INTEGER REAL FLOAT DOUBLE BINARY VARBINARY LONGVARBINARY DATE TIMESTAMP 64 Small fixed length character string Small variable length character string Large variable length character string Fixed precision decimal value represented with a precision that can be greater than the original precision Fixed precision decimal value represented with a precision that is no greater than the original precision A single bit value that can be either zero or one 8 bit unsigned integer 16 bit signed integer 32 bit signed integer 64 bit signed integer Single precision floating point number that supports seven digits of mantissa Double precision fl
52. sr local java latest lib In order to use the HP Driver for J DBC client on the MPE iX system you need to have the J installed J is shipped with MPE iX starting in Release 6 0 The following sub directories for the Driver class files should be automatically created com hp jdbc allbase com hp jdbc allbase samples For a complete list of the files that are installed please refer to Appendix E HP ALLBASE SQL J DBC File Lists 26 Chapter 3 Installation Configuring HP JDBC Server Configuring HP J DBC Server The HP J DBC Server components behave the same on both the HP UX and MPE iX platforms Thus both the configuration file and log file are the same except for filenames The following example shows the standard HP J DBC default server configuration file servcfg This is a text file that resides on the server host in the same directory as the HP J DBC Server executable files and can be used to alter some of the server s behavior LOGFILE opt allbase jdbc logs servlog IMEOUT 7200 LOGGING ERROR LOGGING CONNECTION LOGGING INFO LOGGING WARNING LOGGING FATAL LOGGING IN LOGGING 001 LOGGING INHEX LOGGING OUTHEX LOGGING DEBUG LOGGING TIMESTAMP LOGGING NONE If LOGGING NONE is not commented out it must be the last line for it to have the desired effe
53. supported ALLBASE SQL SQL Statements 49 Dynamic Parameterized SQL Statements 50 Stored Procedures s isesi Rx Rx gm ex bs eee die ae eee ar LEX 51 Contents 6 Troubleshooting Connection Process 2 oe es at ele Gas ade ea NEN RING ea ace a 54 Client Trading eee RE wie Ped eddie Dee ee ee ele el ode OY 56 Server pe rey i titi hike pute awd 60 A Data Types ALL BASE SQL Data Types cee ey RE eked Ge Oil eee cone eae E M RR E 62 1 BBC Data Types s cs seed de RR aon 64 B J DBC Monitor HP UX Monitor ea teenie 66 M PEAX MONITORS oy hale he eroe rese etna der GAA hed a e alt 67 HP UX Monitor Configuration File 68 HP UX Moriltor E Og 5 e AE er gat eR SERIA 70 MPE DC Mon tor Og 25 eet ere x ien EM NUN snes 72 HP UX Monitor Startup and 5 14400 tee 74 Monitor Startup and Shutdown 75 C HP J DBC Server HP DBC Server Configuration File 78 J DBG Server EogS siia ao de enr ade Gah oa ded Oa Eri eh tad d o e cete a 80 D Simple Client Source Code E HP ALLBASE SQL J DBC File
54. tape archive TAR 22 Unsupported ALLBASE SQL Data Types BINARY 47 LONG BINARY 47 LONG VARBINARY 47 unsupported SQL statments 48 UNZIP 25 UNZIP WINZIP package 25 URL Syntax 38 user id 36 user name 36 user password 36 user validation 36 41 userid 40 Using FTP ARPA SYS HP 3000 24 HP 9000 25 Index V value of 0 46 value of 1 46 w command line switch 34 WINZIP 25 2 ZIP 24 ZIP package 25 97
55. tes all of its child processes must be terminated before it can be restarted For this reason the monitor must not be terminated by using the UNIX ki11 command Always use the monct x1 tool to kill the monitor The tool will search out all the child processes and kill them first before killing the monitor Chapter 3 29 Installation Starting and Stopping HP JDBC Monitor MPE iX Monitor Startup and Shutdown The startup and shutdown of the MPE iX HP J DBC Monitor is normally done by the startup and shutdown stream jobs JSTRTMON and JSTOPMON which are normally included in MPE iX system startup and shutdown scripts Thus as long as the machine is up and running the J DBC Monitor will also be up The only time the monitor should ever be brought down is to install a newer version In this case use the JSTOPMON stream job or ABORTJOB command to kill the monitor process The ABORTJOB command to shut down the monitor is ABORTJOB JXX where XX is the job number Torestart the monitor after it was accidentally terminated or shut down use the JSTRTMON stream job to start it up STREAM JSTRTMON JDBC SYS On the platform each client connection is serviced by a Separate process that is in the same session as the monitor Thus if the monitor is terminated or dies the existing server process already serving the dient requests will also die This could result in dient applications experi
56. the UNIX kill command Always use the monctr1 tool to kill the monitor The tool will search out all the child processes and kill them first before killing the monitor 66 Appendix B JDBC Monitor MPE iX Monitor MPE iX Monitor On the MPE iX platform each client connection is serviced by a separate process that is in the same session as the monitor Thus if the monitor is terminated or dies the existing server process already serving the client requests will also die This could result in client applications experiencing dropped connections Appendix B 67 JDBC Monitor HP UX Monitor Configuration File HP UX Monitor Configuration File This represents the standard HP UX J DBC monitor configuration file This is a text file that resides on the server host the same directory as the monitor executable file and can be used to alter some of the monitor s behavior The name of the monitor configuration file is moncfg and it is located opt allbase jdbc bin MLOGPATH opt allbase jdbc logs MAXLOGSIZE 500000 MAXSESSIONS 128 JDBCSERV JDBC SYS SERVICE opt allbase jdbc bin jdbcserv The first configuration option MLOGPATH refers to the path for the monitor log file Normally this is set to opt allbase jdbc logs The log file name for the monitor is called monlog The second configuration option MAXLOGSIZE specifies the maximum size of the monitor log file before the file is backed up to t
57. the dass and method are logged Chapter 6 59 Troubleshooting Server Logging Server Logging The server configuration file controls logging of the DBC Server see Configuring HP J DBC Server in Chapter Installation or HP J DBC Server Configuration Files in Appendix C HP J DBC Server The following is a list of the various logging levels that can be set in the server configuration file NONE CONNECTION THEX IMESTAMP ERROR FATAL EBUG Turns off all preceding logging levels Turns on logging of connection information such as the time of the connection the dient I P address and the name of the database Turns on logging of input information coming from the dient Turns on logging of the hexadecimal dump of the input information coming from the client Turns on logging of output information being sent back tothe dient Turns on logging of the hexadecimal dump of the output information being sent back to the dient Turns on logging of miscellaneous information about the processing of the server This has the potential to log a fair amount of data which may affect performance This causes each log entry to be prefaced by a timestamp of the form HH MM SS mmm Turns on logging of warning messages generated by the Server Turns on logging of error messages generated by the Server This is the same as specifying
58. type uses the J ava numeric conversion routines and any necessary numeric casting Thus this could result in a java lang NumberFormatException being shown when the conversion is performed Again the application developer should take the necessary precautions 46 Chapter 5 ALLBASE SQL Specifics Unsupported ALLBASE SQL Data Types Unsupported ALLBASE SQL Data Types The ALLBASE LONG B INARY and LONG VARI B INARY data types are currently not supported by the HP Driver for J DBC Chapter 5 47 ALLBASE SQL Specifics Acceptable SQL Syntax Acceptable SQL Syntax The HP J DBC Server will accept either ODBC SQL statement syntax or ALLBASE SQL statement syntax All SQL statements are first parsed for ODBC 2 0 SQL syntax If the statement conforms to the ODBC 2 0 SQL syntax it is translated to ALLBASE SQL before being passed to the ALLBASE SQL DBMS If the statement does not conform to the ODBC 2 0 SQL syntax rules it is assumed to be an ALLBASE SQL statment and is passed without modification to the ALLBASE SQL SQL DBMS 48 Chapter 5 ALLBASE SQL Specifics Unsupported ALLBASE SQL SQL Statements Unsupported ALLBASE SQL SQL Statements Table 5 3 shows a list of unsupported ALLBASE SQL statement types in HP J DBC Note that in many cases J DBC provides a standardized method of performing the same action For example the ALLBASE SQL COMMIT WORK
59. uirements The HP J DBC Server components require MPE iX version 6 0 or greater components are only required if your J DBC Server platform is the MPE iX operating system 18 Chapter 2 Requirements ALLBASE SQL or IMAGE SQL Requirements ALLBASE SQL or IMAGE SQL Requirements The HP J DBC Server components require ALLBASE SQL G3 01 or IMAGE SQL G3 00 or greater Chapter 2 19 Requirements ALLBASE SQL or IMAGE SQL Requirements 20 Chapter 2 Installation This chapter takes you through the step by step process of installing and configuring HP Driver for J DBC and its server components 21 Installation HP UX Server Components HP UX Server Components The installation of the DBC Server components must be done by system administrator who has root capability on the server host where the database resides A temporary directory tmp jdbc is used to stage the installation scripts and product tar file The server components are distributed as a UNIX tape archive TAR file hp3dbc XXX tar where xxx represents the release number of the product Once the tar file is copied into staging directory tmp jdbc extract the installation shell script from the tar file 5 tar xf hpjdbo XXX tar install sh Run the installation script install sh to perform the actual installation The installation script will create the necessary directories opt allbase Jjdbc extract the server files and the
60. used to log on to the server host The second connection method alsotakes a J ava String URL but allows for the user name and password to be specified as separate arguments which not logged This would be the more secure method of connecting to the database The choice of method is entirely up tothe application developer The HP Driver for DBC supports both methods Chapter 4 37 Using the HP Driver for JDBC URL Syntax Including User Name and Password URL Syntax Including User Name and Password The first connection method specifies all connection parameters in a URL string including the user name and password java sql DriverManager getConnection url where String url jdbc allbase host port database UID uid amp PWD pwd amp TRACE trace host Name or IP address of the sever host port Optional port number on which the DBC Monitor is listening If not specified the default part number 31700 is used database ALLBASE SQL or IMAGE SQL database name uid Server host userid that is authorized to access the database pwd Server host password that matches the user id provided above trace Optional trace values separated by the vertical bar character For more information on tracing seethe Troubleshooting section This connection method may result in the user name and password being logged in various places as URLs are commonly logged on various web servers and proxy
61. various logging levels is discussed in the Troubleshooting section under Server Logging The logging levels can be turned on and off by either commenting the line out or uncommenting the line If the last lineis LOGGING NONE and the line is not commented out all logging is turned off The server configuration file is read in anew each time a new server process is started Thus oncethe file is edited and saved the changes will take place beginning with the next server process Existing server processes cannot be made to reread the configuration file This is the default server configuration file meaning that all server processes use the same configuration file Thus if logging is turned on in this file it will bein effect for all subsequent server processes Appendix C 79 17039 17039 17039 17039 17039 17039 17039 17039 17039 17039 17039 17039 17039 17039 17039 17039 17039 17039 17039 17039 17039 17039 17039 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 226 726 3263 226 26 26 226 2263 26 226 i26 i26 26 26 26 26 26 26 262 26 26 26 26 14 14 14 14 14 14 14 14 155 l5 IS 15 15 15 155 1 55 195 159 19 19 45 45 45 HP JDBC Server JDBC Server Logs J DBC Server Logs This is a sample of DBC Server log
62. ver Manager Once a driver has been loaded and registered with the Driver Manager it is ready to be used to connect to a database Chapter 4 35 Using the HP Driver for JDBC Connection Parameters Connection Parameters The following information is required for all HP J DBC connections to an ALLBASE SQL or IMAGE SQL database Host name The name of the server host on which the ALLBASE SQL or IMAGE SQL database and J DBC Monitor is running This can either be the name of the server host or the IP address For example myserv mycomp com or 20 45 12 122 Port number The port number on which the DBC Monitor is listening This is specified when the monitor is started on the server host in the startup command or script The default port number for use by the J DBC Monitor is 31700 If a port number is not specified this default value is used User Name A valid user name for the server host The DBC Monitor performs user validation on the server host for all dient connections For the HP UX server platform the user name 16 the same as the HP UX user id For the MPE iX server platform the user name can be composed of a user group account and session id The user name is masked by the DBC Driver before it is transferred over the network to prevent casual detection User Password The password s that match the provided user name For the HP UX server platform this is the password for the provided
63. wn The startup and shutdown of the MPE iX HP J DBC Monitor is normally done by the startup and shutdown stream jobs JSTRTMON and JSTOPMON which are included in system startup and shutdown scripts Thus as long as the machine is up and running the DBC Monitor will also be up The only time the monitor should ever be brought down is to install a newer version In this case use the JSTOPMON stream job or ABORTJOB command to kill the monitor process The ABORTJOB command to shut down the monitor is ABORTJOB J XX restart the monitor after it has accidentally terminated or was shut down use the JSTRTMON stream job to start it up stream JSTRTMON JDBC SYS Appendix B 75 JDBC Monitor MPE iX Monitor Startup and Shutdown 76 Appendix B HP J DBC Server This appendix discusses the HP J DBC Server components the DBC Monitor Unlike the HP J Monitor the HP J DBC Server components behave the same on both the HP UX and MPE ix platforms Thus both the configuration file and log file are the same except for filenames 77 HP JDBC Server HP JDBC Server Configuration File HP J DBC Server Configuration File The following example shows the standard HP J DBC Default Server configuration file This is a text file that resides on the server host in the same directory as the HP J DBC Server executable files and can be used to alter some of the server s behavior On HP UX the name of th

HP Driver for JDBC User`s Manual

Contents

Download Pdf Manuals

Related Search

Related Contents