Conference orarep::nomahs::dbintegrator_public_public

    
    Hi Geoff,
    
    We're going to need a bit of help understanding the problem here.
    
    I'm not sure I understand under what circumstances the dynamic prepare
    works vs. the circumstances it fails.  Could you explain it further? Is
    the statement even making it to DBI?  Could you turn on error tracing
    in DBI and see if there is any indication as to what in DBI is failing
    (define dbi_trace_flags "errors")?  What is a cut (2 cuts vs 5 cuts)?
    
    Thanks,
    
    dan
    

    Sorry Dan for the poorly written note. I'll turn on the DBI flags.
    Cuts, I don't think, are important to understand here. They are more
    application specific. However, when we run against RDB the prepare
    works OK. I'm going to do some more testing of different scenarios
    today. I'll get you more info later. I guess my first note was hoping
    that there was something simple I was missing. 
    
    I'll post more stuff later this morning.
    
    Thanks,
    Geoff
    

    Hi Geoff,
    
    Clay had this problem before, I believe. I think he had a problem where
    all he could run was a single cut, and he increased some parameter in
    the C code and was then able to run 2 cuts. Can't this same parameter
    be tweeked again to allow more cuts?
    
    Dan, a cut is a different rollup of data. One cut would be like a
    customer report, two cuts would be a customer report by geography,
    a three cut report would be customer by geography by sales segment.
    Is that right Geoff?
    
    John

    RE.3 That's right John about the cuts.
    
    Dan, I have no DBI errors in the dbi trace file. The only error we see
    is the one posted earlier. This code is working against Rdb. 
    
    A few things we've seen since this morn:
    
    	We can do more than 3 cuts, it has problems on some SQL statements
    and not others. There really is no visible consistency in the
    situations where it errors (at least not that we've been able to
    identify).
    
    We've looked carefully at the SQL statements that fail and have even
    run them interactively against the DBI database with no problems. 
    I'm still trting to find some common thread when things fail but not
    having much luck.  
    
    Is there anything special about how DBI handles dynamic SQL?
    
    At this point I'm grabbing at anything.
    
    Geoff
    

    
    Hi Goeff,
    
    Can the statements be prepared sometimes and not other times, or are
    they always failing?  Do you think there could be a resource issue?
    
    Could you try other dbi tracing - maybe that will help us figure out if
    the statements are even making it into DBI and not being rejected at a
    higher level.  Could you try just turning on "explain,sdi_brief" trace
    flags?
    
    Thanks,
    
    dan 

    Hi Dan,
    
    yes, some statements can be prepared and some not. Doesn't look like a 
    resource problem, but, who knows. I'll do the other DBI flags in the
    morning and send you the file.  
    
    We wrote a small program to do dynamic sql with the bad statement
    hardcoded in. It worked. That leads me to believe that the buffer
    holding the statement may be bad. However, why does it work with Rdb?
    
    Getting frustrated at this point. I'll talk to you in the morning.
    
    Thanks for all the help,
    Geoff

    Hi Dan,
    
    The Quota command stream you sent didn't show any problems with quotas.
    
    We went back to the old dbkey fetch code to see if that would work for
    the failing select statement. It did not work in DBI but did work in
    Rdb.  
    
    We also wrote a program in C and hardcoded the bad SQL statement into a
    buffer  and we were able to prepare it in DBI.  We did do a memory
    dump of the buffer after that and found no problems with the string in
    the buffer. 
    
    Geoff
    

    
    Hi Geoff,
    
    I just got mail from Clay Crommett with a bugcheck that looks like it
    would be associated with a memory resource problem.  Could you:
    
    kick up pgflquo in authorize
    make sure virtualpgcnt in sysgen is at least as high as the new pgflquo
    
    Thanks,
    
    Dan

    Hi Dan,
    
    You probably already know that kicking up the quotas didn't work. The
    account has very high quotas.
    
    The funny part is that the statement works against DBI when hardcoded
    into the character string to be prepared, but not when the string is
    build from moving data in. Yet, the same built string runs fine against
    a single RDB databasde without DBI.
    
    Is there a possible difference between the way DBI parses the final
    character string before or during "prepare" phase and the way RDB
    alone handles the string to prepare?
    
    Does DBI do anything with the string containing the sql statement to
    prepare, or does it pass it through to RDB to resolve? If DBI does do
    something with the string, is there a way to "pass it on" to RDB,
    similar to EDA/SQL' pass-through functionality?
    
    We are now at the point of throwing in the towel, and going with the
    single RDB database without DBI, unless we can get this problem
    resolved.
    
    We would also welcome a visit from some DBI folks, if it would help
    to expedite this problem.
    
    Thanks,
    	John

    
    Hi John,
    
    Yes, I got mail saying upping the quotas didn't work.  I'm very
    confused though.  I'm not sure if I am working on the statement not
    prepared problem or a bug check which was sent to me via mail.  I
    got a phone call earlier today saying folks weren't even sure the
    statement was getting sent to dbi to be prepared.
    
    Please lets look at one problem at a time.  Regarding the statement
    not prepared problem:
    
    DBI doesn't parse the textual SQL statement per se - we get a DSRI
    request sent by Rdb SQL - it would be worthwhile to to turn on explain
    and fe_qg tracing - fe_qg will tell us what we think SQL sent to us. 
    We then  decompose the statement to send to the underlying database
    management systems. 
    
    So we look at DSRI requests and we build SQL statements from them -
    there is no "pass through" ala EDA.
    
    I know we can't have your data, but it might be useful for you to send
    us sql export files of the databases (without data) and an export file
    of your dbi catalog.  Maybe you can give us just a few rows to work
    with so we can try to reproduce your problem here.
    
    I hope this helps John.
    
    Thanks,
    
    dan
    

Dan,

	There appears to be two problems as of last friday. We still
are having a problem with statements being prepared, and as of friday
were getting Bugchecks on opening a dynamic cursor.

In an earlier note Geoff had indicated that we created some standalone
code to prepare the statement that our production code was having a problem
with. The standalone code had no problem preparing it. Late on friday we
moved this code into our production code and had it execute in the same
area that the production code would prepare a statement. The new included
code would no longer prepare the statement also.

I'll look into sending you our database create streams so you can have a 
database to poke at..

Clay

    Dan,
    
    I can send you export files for all the databases, including the DBI
    logical database, but then comes the problems of trying to set up our
    environment and code, along with trying to show you how it all works.
    
    It would be a lot easier if someone from Oracle DBI could come down
    at your convenience, say in the next week or so.? Is that possible?
    If not, we can try to work something else out.
    
    Thanks,
    	John

    
    Hi John,
    
    I'll send you mail off-line - we'll try to get down there this week.
    
    Thanks,
    
    dan
    

    
    Well it took a while, but the problem here was that the prepare
    statement was sending in a statement id where it should have been
    sending in 0.
    
    dan