Conference orarep::nomahs::dbintegrator_public_public

Dave,

I can take a shot at the second problem.

If the generated tables names are too long you can create a new DBMS subschema
with short aliases for record and set names.

Here an example schema/subschema that I've used to test this problem:

-------------------------------------------------------------------------------
SCHEMA NAME IS DSQLLNAM

AREA IS AREA1

RECORD NAME IS REC1
    WITHIN AREA1
    ITEM NAME IS ITEM1
        TYPE IS CHARACTER 10

RECORD NAME IS THISISAMIGHTYLONGRECNAME1
    WITHIN AREA1
    ITEM NAME IS ITEM2
        TYPE IS CHARACTER 10

RECORD NAME IS THISISAMIGHTYLONGRECNAME2
    WITHIN AREA1
    ITEM NAME IS ITEM3
        TYPE IS CHARACTER 10

SET NAME IS THISISAMIGHTYLONGSETNAME
    OWNER IS REC1
    MEMBER IS THISISAMIGHTYLONGRECNAME1
        INSERTION IS AUTOMATIC
            RETENTION IS FIXED
        ORDER IS FIRST

    MEMBER IS THISISAMIGHTYLONGRECNAME2
        INSERTION IS AUTOMATIC
            RETENTION IS FIXED
        ORDER IS FIRST

-------------------------------------------------------------------------------

Queries on a database with this this schema and a default subschema would have
the problems that you described.

A solution would be to create a new subschema with the ALIAS clause and then use
the alias names in your queries:

--------------------------------------------------------------------------------
SUBSCHEMA NAME IS SHORT FOR DSQLLNAM SCHEMA

ALIAS RECORD THISISAMIGHTYLONGRECNAME1 IS REC2

ALIAS RECORD THISISAMIGHTYLONGRECNAME2 IS REC3

ALIAS SET    THISISAMIGHTYLONGSETNAME  IS FSET

REALM AREA1
    IS AREA1

RECORD NAME IS REC1
    ITEM ITEM1 TYPE IS CHARACTER 10

RECORD NAME IS REC2
    ITEM ITEM2 TYPE IS CHARACTER 10

RECORD NAME IS REC3
    ITEM ITEM3 TYPE IS CHARACTER 10

SET NAME IS FSET

-------------------------------------------------------------------------------

Paul

    
    Hi Dave,
    
    Regarding problem 1, what is being traced?  Did the customer try doing
    a set transaction read only or some such?
    
    Thanks,
    
    dan

re: problem #1

at some point (not sure of the version number) support for DBMS
Access and Allow Mode logicals was added to the DBMS Gateway.

  DBMSQL$ACCESS_MODE [ RETRIEVAL ]

  DBMSQL$ALLOW_MODE [ EXCLUSIVE | PROTECTED | CONCURRENT | BATCH ]

when defined, these logicals override what was specified by a
SET TRANSACTION statement.

DBMSQL$ALLOW_MODE BATCH may help.

/mark

    Thanks All,
    
    Thanks .3 Mark, the customer is trying that, and will let us know if
    	it doesn't fix the problem.
    
    Thanks .2 Dan, thanks, I asked them to turn in tracing for problem 2 
    		long names in the hope that it might capture the problem,
    		but the customer didn't know how to turn on tracing anyway.
    
    Thanks .1 The customer claimes not to use CDD legally (whatever that means!)
    
    		Any other ideas?
    		
    	 
    	Thanks for the ongoing assistance and support.
    
    					Cheers,
    						Dave
    
    
    	The following is from the customer
    
    ------------------------------------------------------------------------
    
    Thanks very much for your information.
    
    We have entered the suggested logicals, and are now just waiting for
    the lock-up to happen again (or not).
    
    As for the suggested solution for the long names problem:
    We happen to have a member of the design team here (from Italy), who
    says that this solution would work if the database used the schemas 
    through CDD.
    Unfortunately, this application does not. And to rebuild (and maintain!) 
    this product in a different way in Italy, and re-install here, does not
    appear to be an attractive solution.
    
    So can the engineers think of another way of using these aliases?
    
    Notes:
    1. We don't even have CDD legally installed on our platfor.
    2. This SQL-DBI access is a specific solution for a Telecom need, not
       directly related to what the (Italian) DBMS application provides.
    
    Hopefully you can find an "enhanced" solution.
    ------------------------------------------------------------------------

    Gidday,
    
    	Here's also more information about the customers use of CDD,
    
    --------------------------------------------------------------------
    
    In Italy (the development system) they have the schemas in CDD and the
    files in the DBMS directory structures. These files are backed up on 
    copied to an installation tape. From this tape the database files are 
    restored in NZ, and structured to the right sizes; CDD remains empty.
    
    If we would have to install CDD as part of solving this problem, we face:
    - quite a bit of time investment here and overseas,
    - changing instalation procedures,
    - the problems of copying CDD,
    - disruption of the system, which after a few delays will DEFINITELY go
      live to control Telecom's NZ data network on 24-AUG.
    
    Both my Italian colleague and myself (on test databases last year) have
    experienced major problems in correctly backing up and restoring CDD
    files, specifically across systems. We would definitely need specific
    assistance on how exactly to go about this.
    
    -----------------------------------------------------------------------
    
    

>    In Italy (the development system) they have the schemas in CDD and the
>    files in the DBMS directory structures. These files are backed up on 
>    copied to an installation tape. From this tape the database files are 
>    restored in NZ, and structured to the right sizes; CDD remains empty.
 
    Sounds OK. You only need CDD for development.
       
>    If we would have to install CDD as part of solving this problem, we face:
>    - quite a bit of time investment here and overseas,
>    - changing instalation procedures,
>    - the problems of copying CDD,
>    - disruption of the system, which after a few delays will DEFINITELY go
>      live to control Telecom's NZ data network on 24-AUG.
    
>    Both my Italian colleague and myself (on test databases last year) have
>    experienced major problems in correctly backing up and restoring CDD
>    files, specifically across systems. We would definitely need specific
>    assistance on how exactly to go about this.
    
    I don't think there is any 'correct' way to backup and restore CDD
    repositories across systems. However it is not necessary.
    
    You can use dbo/export and dbo/modify/import to transfer a subschema to a
    database on a system without CDD.
    
    Peter

    Hi Peter,
    
    	Thanks for the suggestions, to me they sound great, very concise,
    and quite elegant.
    
    	Though the customer doesn't want to do them, I think that they
    'hope' that there is a magical logical that will extend the tablename
    length for them.
    
    	I am recomending that they get some consulting from ORACLE.
    
    				Thanks for your help,
    
    							Dave.
    
    ------------------ from the customer -----------------------------
    
    
    We have studied the suggested DBO/export /import suggestion.
    These tools are completely new to everybody on either side of the
    world. "Export" produces a metadata file; "Modify/import" uses a
    metadata file. The one remaining problem lies inbetween: changing this 
    file, which is more than a matter of simple editing, it seems.
    
    The procedure as we have analysed it:
    1. create a new subschema using ALIAS.
    2. Compile this into CDD using DDL/compile at the development site.
    3. Integrate the new subschema using DBO/modify/subschema=new_subschema
       into the root file.
    4. Extract the metadata from the root file using DBO/export.
    5. Transfer the metadata to the production site.
    6. Integrate the metadata into the local root file using
    DBO/modify/import.
    
    You see the "nuisance factor": step 2, 3, 4 have to be performed in
    Italy, which probably has to go through some bureaucracy as well to obtain
    permisssion. So hopefully you can tell us that we are mistaken, and
    that there is a more direct way of doing it.
    
    
               

    Gidday,
    
    	   I have an update from the customer, with the most wonderful
    assistance of the Sydney Team (working from the most helpfull notere on
    this note), apparently the customer has successfully made the alias in 
    DBMS, and proved that it works from DBMS. However they can't find anywhere 
    in the documentation how to now use this from DBI/SQL, hence they still 
    have the origional problem.
    
    	   Any ideas where to go from here,?
    
    					
    					Cheers,
    						Dave
    
    
    ---------------- From the customer -------------------------
    
    We have succeeded in getting the (alias) subschema into the DBMS root.
    Using DBQ and an enhanced bind statement we can actually use the alias
    names.
    
    Unfortunately we have sofar not been able to find a way to have SQL use
    these aliases, i.e. tell DBI to use the subschema.
    
    The DBQ bind we use is
    > bind dbint_subschema for cgr_database_files
    
    I have not been able to find anywhere in DBI or SQL something that
    mentions alternative subschemas. Seems like a simple question for your 
    team; I hope they agree.
    
    -------------------------------------------------------------------------

Dave,

The Accessing DEC DBMS Data with SQL guide talks about how to do this in section
2.1, Attaching to a Database.

Basically, you can use: '/PATHNAME=<database-name>.<subschema-name>' in the
attach string for interactive SQL or in the definition of the SQL$DATABASE
logical.  If you don't specify a subschema name, the DEFAULT_SUBSCHEMA will be
used.
	
ie: $ DEFINE SQL$DATABASE "/TYPE=NSDS/PATH=cgr_database_files.dbint_subschema"

paul

    Will also need /dictionary_driver=dbmsql$shr  in the attach spec 
    unless you define the logical nsds$default_dictionary_driver to be 
    dbmsql$shr
    
    Don

    Hi,
    
    	Thanks for all the help with the long names issue, it seems to be
    working now.
    
    	Meanwhile they have come back (this morning) with the following
    about the locking issue. They claim to have done what is mentioned in
    note .2
    
    	Any comments? assistance? help?
    
    						Dave.
    
    
    -----------------------------------------------------------------------
    First we thought it's ODBC, then the network, or the server. Probably
    there are at least 2 separate issues. There clearly was a problem with the
    SQLserver that now seems to have been solved with assistance from a local 
    VMS expertise company who have experienced similar problems themselves for 
    the last 3 years.
    However, we still have occasional lock-ups, whether or not going over the
    network, ODBC, or direct SQL. Usually it is less than a minute, and just a
    nuisance (although unacceptable to the customer's criteria). But sometimes 
    it goes for minutes, and we have to kill the SQL user. There can be no
    doubt that it is the DBI product/process that is the cause, as no problems
    have ever been experienced using DBQ in whatever mode or multitude of 
    users.
    
    Yesterday I have been experimenting to gather "facts" but the behaviour
    seems rather random. Doing the same things repeatedly, sometimes it locks,
    sometimes it doesn't. What I did:
    1. start SQL
    2. attach (always read only)
    3. harmless query
    4. dangerous query
    5. exit
    
    "harmless" means not directly working on an area used by most application 
    		users
    "dangerous" works on an area continuously used by most users.
    
    Out of 10 times: 3 times there was no problem at all; 3 times the
    lock-up occured immediately at step 2 (!), 3 times at step 4, once at step 
    3. Always the lock-up only resolves at exit from SQL (step 5). That is:
    once it hangs, it hangs. And this was running SQL on the node itself.
    
    I have again (today) tried to activate the trace as per your original
    request:
                $define dbi_trace_flags "errors,exceptions"
                $define dbi_trace_output "trace.out"
    
    if it gives any result, I'll send it of course, but sofar no sign of a
    file "trace.out". I must do something wrong.