Can connection userid be used as schema?

Help
2010-04-29
2013-04-05
  • David Adler
    David Adler
    2010-04-29

    When I run a query that doesn't specify a schema for a table name, the TE default schema value is pre-pended even if ?SCHEMA? is not used.

    The options dialog can be used to set the schema to "" which allows the query to execute but there doesn't appear to be any way to set this with an SQLExecutionOptions attribute.

     
  • I'm sorry you are going to need to be a little more specific, maybe even provide an example.

    By default DB2 will run statement under the schema of the user ID of the user who is currently connected to the database. You can use the TE set schema option under file or set a default schema on the options panel of the Ad hoc. The TE does not have the knowledge to perpend statements with a schema. The ?SCHEMA? is an old method we used to substitute a schema name within a tutorials and the substitution takes place before the SQL to presented to the user in the Ad Hoc. A unique schema is used in tutorials to isolate each tutorial some what from the environment to give you a bit of a sand box. Tutorials are the only place we override the default schema. All other instances will require the user to directly specify a schema the wish to run under or the database default will be used.

    If a schema is specified I set the default schema using the command

    SET CURRENT SCHEMA
    and
    SET CURRENT PATH

    If you are working within a tutorial yes you are right the SQLExecutionOptions does not have an override for the Tutorial schema. Tutorials run under one schema. This will leave you with two options.

    1. Fully qualify all statement that you wish to run under a particular schema
    and / or
    2. Disable the tutorial schema

    To disable the tutorial schema set the following attribute on the tutorial element of the tutorial.xml file
    disableSetSchema="true"

    I hope this helps.
    Regards,
    Matthew

     
  • David Adler
    David Adler
    2010-04-29

    I'm creating a tutorial to run against the tables that are created by running the Spatial Extender sample
    sqllib/samples/extenders/spatial/runGseDemo
    When this is run, it creates the tables without specifying a schema so it typically creates them using the connection userid.

    This is the query that I am running in the tutorial:
    SELECT R.NAME, COUNT(C.NAME) AS CUSTOMERS \n \
            FROM CUSTOMERS C, REGIONS R \n \
            WHERE DB2GSE.ST_WITHIN(C.LOCATION, R.LOCATION) = 1 \n \
            GROUP BY R.NAME ORDER BY R.NAME
    ;

    When running in the tutorial environment, it pre-pends a unique schema which isn't helpful in this case.

    It sounds like the answer is to set disableSetSchema="true" in the tutorial.xml.

    (It would probably be better if runGseDemo set an explicit schema but unfortunately it doesn't and this is what currently ships)

     
  • David Adler
    David Adler
    2010-04-29

    Setting disableSetSchema="true" did the trick.

    Thank you for the assistance.