J2EE Connector Architecture. Table 3-1 lists the JDBC connection properties supported by the DB2 driver. Because the Oracle Type 4 JDBC DB2 driver.
Setting JDBC properties in Sterling's connection URL This has to be done if Sterling's built-in connection pooling is used ( -Dvendor=shell). It is used in all agent and integration servers, and can also be used in the application server. In Sterling's customeroverrides.properties file, edit your JDBC connection URL and append the property: jdbcService.db2Pool.url=jdbc:db2://host:port/dbname:property=value; Example: jdbcService.db2Pool.url=jdbc:db2://DB2SERVER:50000/SSFSDB:traceLevel=4;traceFile=myFile.log; Please watch the punctuation here. After the database name, there is a colon (:), and after every property will be a semi colon (;).
Setting JDBC properties in WebSphere Application Server If the Sterling application (not agent or integration server) uses WebSphere's connection pooling ( -Dvendor=websphere), then you have to set the property in the corresponding WAS screen: Integrated Solutions Console Resources JDBC Data Sources yourdatasource Custom properties New Name: Value: Type: Example: Name: traceLevel Value: 4 Type: java.lang.Integer.
Db2 Jdbc Drivers
. In Driver.connect, specify the following string and the Properties object as parameters: jdbc:bea:db2://:;LocationName= RelationalDatabaseName In this example, RelationalDatabaseName is the value of Database obtained from the result of running the WRKRDBDIRE command. The following is an excerpt of the Java client.
Properties props = new Properties; props.put('user', user); props.put('password', password). MyDriver = (Driver)Class.forName('weblogic.jdbc.db2.DB2Driver').newInstance; conn = myDriver.connect('jdbc:bea:db2://10.1.4.1:446;LocationName=S10B757B', props); stmt = conn.createStatement; stmt.execute('select. from MYDATABASE.MYTABLE'); rs = stmt.getResultSet. Super mario 64 nds. Creating a DB2 Package A DB2 package is a control structure on the DB2 server produced during program preparation that is used to execute SQL statements. The DB2 driver automatically creates all DB2 packages required at connection time.
If a package already exists, the driver uses the existing package to establish a connection. Note: The initial connection may take a few minutes because of the number and size of the packages that must be created for the connection. Subsequent connections do not incur this delay. By default, DB2 packages created by the DB2 driver contain 200 dynamic sections and are created in the NULLID collection (or library). In most cases, you do not need to create DB2 packages because the DB2 driver automatically creates them at connection time.
If required, you can create DB2 packages in either of the following ways:. Manually force the DB2 driver to create a package using the WebLogic Server dbping utility.
Automatically create a package by setting specific connection properties in the connection URL. Note: Your user ID must have CREATE PACKAGE privileges on the database, or your database administrator must create packages for you.
Your user ID (the user ID listed in the JDBC data source configuration) must be the owner of the package. The user ID creating the DB2 packages must have BINDADD privileges on the database.
Consult with your database administrator to ensure that you have the correct privileges. Creating a DB2 Package Using dbping To create a package on the DB2 server with the WebLogic Type 4 JDBC DB2 driver, you can use the WebLogic Server dbping utility.
The dbping utility is used to test the connection between your client machine and a DBMS via a JDBC driver. Because the WebLogic Type 4 JDBC DB2 driver automatically creates a DB2 package if one does not already exist, running this utility creates a default DB2 package on the DB2 server.
For details about using the dbping utility to create a DB2 package, see. Creating a DB2 Package Using Connection Properties You can create a DB2 package automatically by specifying specific connection properties in the initial connection URL. Lists the connection properties you should use in your initial connection URL when you create a DB2 package: Note: This method is not recommended for use with WebLogic Server JDBC data sources because every connection in the data source uses the same URL and connection properties. When a JDBC data source with multiple connections is created, the package would be recreated when each database connection is created.
Table 3-2 Connection Properties for an Initial Connection URL When Creating DB2 Packages Property Database CollectionId= collectionname (where collectionname is the name of the collection or library to which DB2 packages are bound) DB2 OS/390 and iSeries CreateDefaultPackage=true DB2 UDB, OS/390, and iSeries ReplacePackage=true DB2 UDB DynamicSections= x (where x is a positive integer) DB2 UDB, OS/390, and iSeries Using CreateDefaultPackage=TRUE creates a package with a default name. If you use CreateDefaultPackage=TRUE, and you do not specify a CollectionId, the NULLID CollectionId is created. Note: On DB2 UDB, you must use ReplacePackage=TRUE in conjunction with CreateDefaultPackage to create a new package; however, if a package already exists, it will be replaced when using ReplacePackage=TRUE.
Example for DB2 UDB: The following URL creates DB2 packages with 400 dynamic sections. If any DB2 packages already exist, they will be replaced by the new ones being created. Jdbc:bea:db2://server1:50000;DatabaseName=SAMPLE; CreateDefaultPackage=TRUE;ReplacePackage=TRUE;DynamicSections=400 Example for DB2 OS/390 and iSeries: The following URL creates DB2 packages with 400 dynamic sections.
Jdbc Properties File
Jdbc:bea:db2://server1:50000;LocationName=SAMPLE;CollectionId=DEFAULT; CreateDefaultPackage=TRUE;DynamicSections=400 Notes About Increasing Dynamic Sections in the DB2 Package A dynamic section is the actual executable object that contains the logic needed to satisfy a dynamic SQL request. These sections are used for handles and prepared statements and the associated result sets. In some cases, you may need to create DB2 packages with more than the default number of dynamic sections (200). Consider the following information if your application requires DB2 packages with a large number of dynamic sections:. Creating DB2 packages with a large number of dynamic sections may exhaust certain server resources. In particular, you may need to increase the database parameter PCKCACHESZ to allow the larger packages to be created. The creation of more dynamic sections will slow down the initial creation of the DB2 package.
Using DB2 packages with a large number of dynamic sections may impact application performance. If a small number of sections are in use at one time, there will be no impact on the application. If a large number of sections are in use at one time, the performance of the application may decrease because the database will expend resources to check all open sections for locks. As the number of open sections increases, so does the likelihood that a deadlock situation may occur. If your application is mostly executing select statements, it is best to operate in the default mode of automatically committing the database. Dynamic sections are not freed in the DB2 package until the database is committed even if the statements are closed in the application.
In this mode the database will commit every time a SQL statement is executed and free all of the sections that were opened. If you need to operate in a manual commit mode, then it is advisable to commit the database as often as possible to ensure that all server resources are freed in a timely manner. Statements cached in the WebLogic Server prepared statement cache will keep sections in use so that the prepared statements can be reused. The DB2 server has a limit on dynamic sections.
It is possible to try to create more sections than the server will allow you to create. Data Types lists the data types supported by the DB2 driver and how they are mapped to JDBC data types. Table 3-3 DB2 Data Types DB2 Data Type JDBC Data Type Bigint BIGINT Blob BLOB Char CHAR Char for Bit Data BINARY Clob CLOB Date DATE DBClob CLOB Decimal DECIMAL Double DOUBLE Float FLOAT Integer INTEGER Long Varchar LONGVARCHAR Long Varchar for Bit Data LONGVARBINARY Numeric NUMERIC Real REAL Rowid VARBINARY Smallint SMALLINT Time TIME Timestamp TIMESTAMP Varchar VARCHAR Varchar for Bit Data VARBINARY 1. Bigint data type is supported only for DB2 UDB 8.1. 2. Blob data type is supported only for DB2 UDB 8.1, DB2 OS/390, and DB2 iSeries V5R2 (see ).
3. DBClob data type is supported only for DB2 UDB 8.1, DB2 7.x OS/390, and DB2 iSeries V5R2 (see ). 4. Rowid data type is supported only for DB2 OS/390 and DB2 iSeries V5R2. See for more information about data types.
Using a Non-Default Schema for Catalog Methods To improve performance, views of system catalog tables can be created in a schema other than the default catalog schema. Setting the CatalogSchema property to a DB2 schema that contains views of catalog tables allows the driver to use those views.
To ensure that catalog methods function correctly, views for the catalog tables listed in must exist in the specified schema. The views that are required depend on your DB2 database.