sql default schema

Hello,

when using embedded SQL in JVM-Cobol I do have the problem that most of the existing Cobolsources (> 8000) do not have the "FullQualified"  Tablename in the FROM Clause of their SQL-Statements.

With "native" Cobol it takes the username as the default Schema Qualifier. And if this is not sufficent it is possible to set QUALIFIER (as an SQL-Compiler Option).

This is not working with managed Cobol anymore. 

I do want to avoid the need to change all SQL-Statements to fully qualify the tablename. 

Does anybody have the same problem and if yes .... maybe a solution ?

Kind regards

Michael

  • 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.

  • Verified Answer

    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