sql default schema

Hi

Does this syntax help

url=jdbc:db2://test1:60000/testdbprod:currentSchema=myschema1;

currentSchema

Tony

Hello,

I have checked your proposal. The database is now specified as a JBOSS Datasource like this :

<?xml version="1.0" encoding="UTF-8"?>

<datasources xmlns="www.jboss.org/.../schema">

    <datasource jta="false" jndi-name="java:jboss/jdbc/DB2LV1871_TYPE4"
        pool-name="java:jboss/jdbc/DB2LV1871_TYPE4" enabled="true" use-java-context="true" use-ccm="false">

        <connection-url>jdbc:db2://ryker.muc.lv1871.de:50003/vvsk1:currentSchema=test;</connection-url>

        <!-- this must me commented out. Otherwiese youl receive "Property cannot be overwritten by url" (Which is plausible)

        <connection-property name="currentSchema">test</connection-property>

        It is not working with this setting either -->

        <connection-property name="dateFormat">3</connection-property>
        <driver>com.ibm.db2.jcc</driver>
        <security>
            <user-name>XXXX</user-name>
            <password>XXXX</password>
        </security>

    </datasource>

</datasources>

Even with the Schema as a Property in the URL the SQL-Statements in Cobol are returning "-204" (Table or view does not exist).

I do have to hardcode in Cobol :

select bla

from test.myfinetable;

using just

select bla

from myfinetable;

Results in error code "-204"

Kind regards

Michael

Hello,

for preprocessed programs I could imagine compiler options like this :

SQL(DBMAN=JDBC) SQL(NOAUTOCOMMIT) SQL(CHECK=true) SQL(DB=jdbc:db2://zzzzz.muc.xxxxx.de:50003/vvsk1) SQL(PASS=xxxx.xxx) SQL(TARGETDB=DB2) 

SQL(QUALIFIER=test)

The DB2 Option QUALIFIER is the point. This sets the "default" schema name for Statements that do not include the fully qualified Table Name.

Kind regards

Michael

Hi Michael,

I spoke to development regarding this and for managed JVM code the connection properties are not controlled by OpenESQL but need to be set within the connection definition itself.

Using ‘schemaName=x;’ in the connection URL works with Simple-JNDI data sources, We have tested this configuration in-house.

Looking at the JBoss AS 7 docs it looks as though JBoss data sources have a very restricted set of properties that you can set, so this may not be possible as a simple ‘pass through’ property.

We did notice a “new-connection-sql”  tag in www.ironjacamar.org/.../datasources_1_0.xsd , where you can pass off commands to the DBMS vendor before giving the connection to the application.

That may help, if this cannot be set-up as a connection attribute/property (as you are trying to do now)

Question: have you tried to use this same connection from a Java application instead of COBOL?

If the same problem occurred then that would indicate that this was a JBoss problem instead of an OpenESQL issue.

We would then recommend that you contact the folks at JBoss to see what they recommend.

Thanks.

I have tested this with pure Java using the following Connection. Same Results. I do have to specify test.<tablename> in Cobol.

public void setUp() throws Exception {

Class.forName("com.ibm.db2.jcc.DB2Driver");
connection = DriverManager.getConnection(
"jdbc:db2://dbserver:50003/vvst1:currentSchema=test;", "user",
"passwordt");

}

Can you provide me with your test sources. To perform some further tests.

The Schema specified in the Datasource URL is case sensitiv doing

public void setUp() throws Exception {
Class.forName("com.ibm.db2.jcc.DB2Driver");
connection = DriverManager.getConnection(
"jdbc:db2://dbserver:50003/vvst1:currentSchema=TEST;", "user",
"passwordt");
}

does the job. This is also valid for the JBOSS-Datasource. 

<?xml version="1.0" encoding="UTF-8"?>
<datasources xmlns="www.jboss.org/.../schema">

<datasource jta="false" jndi-name="java:jboss/jdbc/DB2LV1871_TYPE4"
pool-name="java:jboss/jdbc/DB2LV1871_TYPE4" enabled="true" use-java-context="true" use-ccm="false">

<connection-url>jdbc:db2://ryker.muc.lv1871.de:50003/vvsk1</connection-url>

<connection-property name="currentSchema">TEST</connection-property>
<connection-property name="dateFormat">3</connection-property>
<driver>com.ibm.db2.jcc</driver>
<security>
<user-name>test</user-name>
<password>tset</password>
</security>

</datasource>

</datasources>

Does the job. Note "TEST" instead of "test". Thanks to Kim Hoskin from MF for helping me on that issue.

Michael