Conference orarep::nomahs::dbintegrator_public_public

The typical cause for long connection times is the volume of metadata info that 
needs to be downloaded from the DB2 database on connect. The /TABLE mechanism
should cut that down.

Try setting up a gateway trace (see the help on tracing on how to do this)

Something like 

$ DEFINE DBI_TRACE_FLAGS "EXPLAIN,ERRORS,DB2_REQ_SQL,DB2_REQ_META" 

should do the trick. It will show all the sql sent to DB2 and the metadata
transfers with timestamps. A more detailed trace (at the SNA level) may then
help isolate the specific bottlenecks. 

The next step (which I would personally recommand) is to use the Rdb7
"Distributed Option" (nee DBI). It comes free with Rdb7 and will make it
possible for DB2 metadata to be permanently retained in its own repository.
Connections will be much faster (since the metadata will no longer be
downloaded from DB2 on each connect). Of course, the definitions in the DBI
repository will need refreshing when they change on DB2, but this is a small
price to pay. 

In addition, you will be able to augment the metadata (by adding your own
views in the DBI repository for example) without requiring any intervention
from your friendly DB2 DBA. ;-)

/albert

    I agree with -1 entirely.  Another thing to watch out for is if the DB2 
    tables have lots of columns, an application asks for dbkey info, and
    you are using the default dbkey mode (read only).  We had one DB2
    customer that had this situation and they noticed better performance
    when they changed their dbkey mode in a config file.  This is mentioned
    in chapter 11 of the Product Family Users Guide.  
    
    Just something else to watch out for.
    
    Don

    Hi,
    
    	The customer tried to use /table but he said it didn't make any
    difference. I'll define the logical to see what happens. But if I use
    /table, I can still access another table that was not defined in
    /table=(xxx,yy).
    
    	Is this correct ? In that case the metadata is downloaded when it's
    needed ?
    
    Thks ..Marcia

    Hi Marcia,
    
    I notice that you don't supply a DB2 user name with /user.  Is this
    because of how attachsec(identify) is configured (pg 4-5 in the V7
    Product Family Users Guide), or is there no security enabled to get to
    the mainframe?
    
    I don't know how the gateway would import metadata if there is no
    security on the mainframe.  In other words, if I say /USER=DON then do
    I only get all the tables created by DON?  If there's no security
    involved and I don't add /USER then does the gateway try and import all
    metadata that is world accessible?  I really don't know the answer to
    this (our mainframe access requires a DB2 username when attaching).
    
    The best thing to do is to turn on the metadata trace flags as Albert
    suggested.  Maybe adding a /USER could help?
    
    Don

    Hi Albert and Don,
    
    	It seems that the fact of using/table makes no difference. We made
    the test with the trace and all the tables are downloaded.
    
    	They really don't use /user. They will check with IBM people how the
    security is defined. Anyway, if the user was defined and he had access
    to all the tables, would it be different ?
    
    	I put below part of the trace file showing that many tables were
    downloaded. The whole log is in VAXRIO::table.log. And the trace
    without /table is there too , it's complete.log
    
      
    Marcia
    Brief dump of table TCARTAO 
    Brief dump of table TCHEQUE 
    Brief dump of table TJUSTPENAL 
    Brief dump of table TCULTURA_RURAL 
    Brief dump of table TPRODUTO_RURAL 
    Brief dump of table TSEGURO 
    Brief dump of table TVEICULOS 
    Brief dump of table TSEGUROS 
    Brief dump of table TRENDA_PRINCIPAL 
    Brief dump of table TPARTICIPACOES 
    Brief dump of table TOUTRAS_RENDAS 
    Brief dump of table TCOMPROMISSOS 
    Brief dump of table TBENS_IMOVEIS 
    Brief dump of table TARRENDAMENTO 
    Brief dump of table TOUTROS_BENS 
    Brief dump of table TATIV_RURAL 
    Brief dump of table TREPRESENTANTES 
    Brief dump of table TDADOS_COMP 
    Brief dump of table TADMINISTRADORES 
    Brief dump of table TPENAL 
    Brief dump of table TCONTROLE 
    Brief dump of table TRELDEP 
    Brief dump of table THISTENDER 
    Brief dump of table THISTCLIENTE 
    Brief dump of table THISTINDICE 
    Brief dump of table TFILIAL 
    Brief dump of table TDEPENDENTE 
    Brief dump of table TCONJUGE 
    Brief dump of table TJURIDICA 
    Brief dump of table TFISICA 
    Brief dump of table TRAMPALCH 
    Brief dump of table TOCUPPRINC 
    Brief dump of table TRAMOATIV 
    Brief dump of table TOCUPALCH 
    Brief dump of table TCLIENTE 
    Brief dump of table TINFOADIC 
    Brief dump of table TIMOVEL 
    Brief dump of table TENDERECO 
    Brief dump of table TVEICULO 
    Brief dump of table TREFERENCIAS 
    

    Hi Albert and Don,
    
    	The customer just called me to confirm that they use
    attachsec(identify) in CICS so that every connection from dbi uses a
    generic user cicsuser that has access to all the tables.
    
    	Marcia

The /table, /database and /user qualifiers are all interrelated when it
comes to limiting tables during metadata imports.  I believe
there was a section in the Users Guide which explained when and how
each qualifier is applied.

I can't remember all the gotchas right now, but I think I can guess what's
going on in this particular case.  The base note gave the attach string as

    /type=db2/database=xxx/access_name=yyy/node=snagwy

and the subsequent notes imply that /table was added to this attach string
with no change in behavior.  It turns out that when /database= and
/table= are specified together, then /database takes precedence in the
definition of which tables to import.  So simply adding /tables to
the attach string had no effect on speed.  (In case you're wondering
why /table is still allowed, well it is still used to apply dbkeys to
tables in this database).

So, to speed up this particular case, remove /database and code
/tables=(xxxxx.yyyyyyy,aaaaa.bbbbb).  You'll need the full table name since
/user is not specified.  This will limit the imports to only those
tables explicitly named and it should be faster.

    Right as rain Dave!
    
    Thanks,
    Don

    Hi,
    
    	I'll try this right now .. Thanks ..Marcia

    Hi,
    
    	I used the whole attach command without the /database and in
    /table , I tried , /table=(database-name.Tcliente).
    
    	It returned me a syntax error. How do I use this qualifier without
    passing the database name. The syntax error says that there is no
    database known or something like this.
    
    	Thanks ...Marcia
    
    PS: I don't have the manuals.

    Marcia,
    
    It's /table=(db2_creator_name.table_name)
    
    not /table=(db_name.table_name)
    
    Don

    Marcia,
    
    You specify who is the owner/creator of the table, then separate that
    from the real tablename with the period.
    
    Don

    Hi ,
    
     I don't have to put the database name somewhere ? Just using
    /tables=(db2creatorname.table) will be enough for db2 to know which
    database to use?
    
    Marcia

>     I don't have to put the database name somewhere ? Just using
>    /tables=(db2creatorname.table) will be enough for db2 to know which
>    database to use?

In real DB2 SQL, users don't need to know the database at all.
Tables are uniquely identified by the owner.tablename combo.
/database is our own way to scope metadata imports.

By the way, I recall that some versions of SQL didn't like
the parens in /table=(aaaaa.bbbbb) when it appeared at the end of the
attach string.  If you're getting syntax errors, try moving
it earlier in the string, for instance, right after /type=db2.