Powerhouse + Oracle-9i + AIX - Response issues
Goparaju, Vidyasagar
VGoparaju at tradepointsystems.com
Thu Sep 1 18:54:28 CDT 2005
Hi Joe,
Thanks again for all your suggestions.
Hope things would get better soon.
Best regards,
Sagar Goparaju
-----Original Message-----
From: Joe Boyle [mailto:joeboyle_adt at hotmail.com]
Sent: Thursday, September 01, 2005 6:15 PM
To: Goparaju, Vidyasagar
Cc: powerh-l at lists.sowder.com
Subject: Re: Powerhouse + Oracle-9i + AIX - Response issues
the qtp syntax is simply as below
req q1
sql IN mygo call wrtemin
req q2
..
regards, Joe.
----- Original Message -----
From: "Joe Boyle" <joeboyle_adt at hotmail.com>
To: "Goparaju, Vidyasagar" <VGoparaju at tradepointsystems.com>
Cc: <powerh-l at lists.sowder.com>
Sent: Friday, September 02, 2005 12:05 AM
Subject: Re: Powerhouse + Oracle-9i + AIX - Response issues
> if the results are still poor, you could always call sqlplus via a script
> file passing in parameters to either populate a table directly, or even
> have one of your qtp passes call a stored procedure to populate the table
> in one of the requests.
>
> drop table temp_table;
> create table temp_table (column_list);
> insert into temp_table (column_list)
> select /*+ index(imp_conn_files MASTER_CUST_EXTRACTED) */
> column_list from imp_conn_files
> where extracted in ('N',' ') and (date_available between 0 and
> 20050901 );
> commit;
>
> Both methods should allow the hint/s to be processed.
>
> regards, Joe.
>
>
> ----- Original Message -----
> From: "Deskin, Bob" <Bob.Deskin at cognos.com>
> To: "Goparaju, Vidyasagar" <VGoparaju at tradepointsystems.com>; "Joe Boyle"
> <atla38 at dsl.pipex.com>
> Cc: <powerh-l at lists.sowder.com>
> Sent: Thursday, September 01, 2005 6:49 PM
> Subject: RE: Powerhouse + Oracle-9i + AIX - Response issues
>
>
>>I don't have much to add here. The ORDERBY may prompt the database to
>> use an index. It all depends on the optimizer and what the history has
>> been. I mean, if the chain is very short, it may be faster to read the
>> data and then sort. Alternatively, reading down the chain may take
>> longer but may be offset by the lack of a sort. Of course it depends on
>> how you're selecting data as well.
>>
>> But certainly, one of the optimization techniques is to get the database
>> to do the sequencing and then use SORTED in QUIZ or QTP.
>>
>> Generally the more you can push down to the database the better.
>>
>> Bob
>>
>> -----Original Message-----
>> From: powerh-l-bounces+bob.deskin=cognos.com at lists.sowder.com
>> [mailto:powerh-l-bounces+bob.deskin=cognos.com at lists.sowder.com] On
>> Behalf Of Goparaju, Vidyasagar
>> Sent: September 1, 2005 1:37 PM
>> To: Joe Boyle
>> Cc: powerh-l at lists.sowder.com
>> Subject: RE: Powerhouse + Oracle-9i + AIX - Response issues
>>
>>
>> I am pretty sure it would help. Because my QTP request has SORT on
>> FILE_NO_ALPHA. I think I could change that to SORTED on and save some
>> time on that part.
>>
>>
>> Thanks
>> Sagar
>>
>> -----Original Message-----
>> From: Joe Boyle [mailto:atla38 at dsl.pipex.com]
>> Sent: Thursday, September 01, 2005 12:33 PM
>> To: Goparaju, Vidyasagar
>> Cc: powerh-l at lists.sowder.com
>> Subject: RE: Powerhouse + Oracle-9i + AIX - Response issues
>>
>> I wonder if it's the presence of the 'order by' syntax that is prompting
>> the optimizer to use the index - assuming that it is.
>>
>> The selection range below seems as though it would select the majority
>> of rows (it seems highly inclusive), and the optimizer might find a full
>> table scan most efficient, that is until you include the 'order by'
>> overhead,
>>
>> extracted in ('N',' ') and date_available >= 0 and date_available <=
>> 20050901
>>
>> Do you see any improvement if you add 'order by' syntax to your quiz
>> cursor?
>>
>> Regards, Joe.
>>
>>
>> -----Original Message-----
>> From: powerh-l-bounces+atla38=dsl.pipex.com at lists.sowder.com
>> [mailto:powerh-l-bounces+atla38=dsl.pipex.com at lists.sowder.com] On
>> Behalf Of Goparaju, Vidyasagar
>> Sent: 01 September 2005 18:00
>> To: Joe Boyle
>> Cc: powerh-l at lists.sowder.com
>> Subject: RE: Powerhouse + Oracle-9i + AIX - Response issues
>>
>> Joe,
>>
>> Thanks for your input. That did help me understand the difference
>> between various calls to SQL.
>>
>> Choose viaindex does send out an ORDERBY clause. No hint to SQL on the
>> index usage.
>>
>>> access imp_conn_files in asa
>>> choose viaindex MASTER_CUST_EXTRACTED extracted "N"
>>> set rep nolimit
>>> go
>>
>> Looks as following when it is sent to SQL
>>
>> select "EXTRACTED", "DATE_AVAILABLE", "FILE_NO_ALPHA", "MASTER_CUST"
>> from "TPSDBA" ."IMP_CONN_FILES"
>> where (:S1 = "EXTRACTED")
>> order by "EXTRACTED", "DATE_AVAILABLE", "FILE_NO_ALPHA",
>> "MASTER_CUST"
>>
>> Sagar
>>
>> -----Original Message-----
>> From: Joe Boyle [mailto:atla38 at dsl.pipex.com]
>> Sent: Thursday, September 01, 2005 11:30 AM
>> To: Goparaju, Vidyasagar
>> Cc: powerh-l at lists.sowder.com
>> Subject: RE: Powerhouse + Oracle-9i + AIX - Response issues
>>
>> Add 'set list sql' to your quiz source file,
>> or use 'quiz auto=... dbaudit=file' ( or even dbaudit=full)
>>
>> Regards, Joe.
>>
>>
>> -----Original Message-----
>> From: powerh-l-bounces+atla38=dsl.pipex.com at lists.sowder.com
>> [mailto:powerh-l-bounces+atla38=dsl.pipex.com at lists.sowder.com] On
>> Behalf Of Goparaju, Vidyasagar
>> Sent: 01 September 2005 17:08
>> To: Deskin, Bob
>> Cc: Whittall, Conrad; powerh-l at lists.sowder.com
>> Subject: RE: Powerhouse + Oracle-9i + AIX - Response issues
>>
>> Hi Bob,
>>
>> What is the difference between the dynamic SQL generated by
>> Powerhouse/Quiz with and without a VIAINDEX on a CHOOSE statement?!
>>
>> Is there a way to see that syntax? I know DBAUDIT helps only if you
>> write SQL cursor within QUIZ. I would like to see the SQL code generated
>> for Oracle at run time.
>>
>> Thanks
>> Sagar Goparaju
>>
>>
>> -----Original Message-----
>> From: powerh-l-bounces+vgoparaju=tradepointsystems.com at lists.sowder.com
>> [mailto:powerh-l-bounces+vgoparaju=tradepointsystems.com at lists.sowder.co
>> m]On
>> Behalf Of Deskin, Bob
>> Sent: Thursday, September 01, 2005 10:52 AM
>> To: Whittall, Conrad; powerh-l at lists.sowder.com
>> Subject: RE: Powerhouse + Oracle-9i + AIX - Response issues
>>
>> I don't recall that we support the use of index hints. There's certainly
>> nothing in the manual from prior to 8.3 and nothing like that has been
>> added more recently. PowerHouse prefers to push as much as possible on
>> the database for a select and let's the database decide how to retrieve.
>>
>> Bob
>>
>> -----Original Message-----
>> From: powerh-l-bounces+bob.deskin=cognos.com at lists.sowder.com
>> [mailto:powerh-l-bounces+bob.deskin=cognos.com at lists.sowder.com] On
>> Behalf Of Whittall, Conrad
>> Sent: September 1, 2005 11:03 AM
>> To: powerh-l at lists.sowder.com
>> Subject: RE: Powerhouse + Oracle-9i + AIX - Response issues
>>
>>
>>
>> I believe that index hints are part of Oracle's own implementation of
>> SQL, and are not part of the ANSI SQL standard...which is the dialect of
>> SQL that PowerHouse understands.
>>
>> You need to stick with ANSI standard SQL (SQL92 I think, although if
>> conformance has been updated maybe Bob D. can jump in here).
>>
>> Best regards,
>> Conrad
>>
>> Conrad Whittall
>> Senior Solutions Architect, Global Customer Services, Cognos
>> Incorporated 3755 Riverside Drive, Ottawa, Ontario, K1G 4K9, Canada
>>
>> -----Original Message-----
>> From: powerh-l-bounces+conrad.whittall=cognos.com at lists.sowder.com
>> [mailto:powerh-l-bounces+conrad.whittall=cognos.com at lists.sowder.com] On
>> Behalf Of Goparaju, Vidyasagar
>> Sent: Thursday 1 September 2005 10:49
>> To: Robert Edis
>> Cc: powerh-l at lists.sowder.com
>> Subject: RE: Powerhouse + Oracle-9i + AIX - Response issues
>>
>> My SQL query thru SqlPlus works fine. But gives the following error in
>> Quiz:
>>
>> $ quiz auto=cur2.qzs
>> Q U I Z (PowerHouse 8.43.D1)
>> Copyright 2005 COGNOS INCORPORATED
>> Licensed PH8-UNIX-RUNTIME-W-REPORTING for customer: 0007140001 GENERAL
>> MOTORS IU
>>
>>> declare imp_conn cursor for &
>>> select /*+ index(imp_conn_files MASTER_CUST_EXTRACTED) */ * from
>>> imp_conn_files & where extracted in ('N',' ') and & date_available
>>> >= 0 and date_available <= 20050901 access imp_conn
>> *E* Error parsing SQL statement.
>> DMS-E-GENERAL, A general exception has occurred during operation
>> 'prepare request'. DMS-E-SS_SYNTAX, A syntax error was detected near
>> '/'.
>>
>>
>> What is wrong with my cursor syntax? My intension is to use Index Hint.
>>
>> Without the index hint, my query works fine. But it is taking longer
>> than simple quiz request with a choose viaindex.
>>
>> Thanks very much
>> Sagar Goparaju
>>
>>
>> -----Original Message-----
>> From: powerh-l-bounces+vgoparaju=tradepointsystems.com at lists.sowder.com
>> [mailto:powerh-l-bounces+vgoparaju=tradepointsystems.com at lists.sowder.co
>> m]On Behalf Of Robert Edis
>> Sent: Wednesday, August 31, 2005 8:20 PM
>> To: PH List
>> Subject: RE: Powerhouse + Oracle-9i + AIX - Response issues
>>
>> You may have to do some logic recoding and push more of the work to the
>> database. What I mean is writing cursors to use in the access
>> statements.
>>
>> Robert J Edis
>> Principal Consultant
>> Robert Edis Consulting
>> P.O. Box 676
>> Deerfield IL 60015
>> USA
>> 1 (847) 612-3863 - USA
>> 61 (41) 549-1680 - Australia
>> RobEConsult at sbcglobal.net
>>
>>
>> -----Original Message-----
>> From: powerh-l-bounces+robeconsult=sbcglobal.net at lists.sowder.com
>> [mailto:powerh-l-bounces+robeconsult=sbcglobal.net at lists.sowder.com]On
>> Behalf Of Goparaju, Vidyasagar
>> Sent: Wednesday, August 31, 2005 4:11 PM
>> To: powerh-l at lists.sowder.com
>> Subject: Powerhouse + Oracle-9i + AIX - Response issues
>>
>>
>> We are in the process of migration from AS400 Powerhouse to Powerhouse
>> on AIX under Oracle environment.
>>
>>
>>
>> We have successfully converted our application and almost ready for
>> the production move. But we have been experiencing response issues.
>> Powerhouse under Oracle environment is relatively very slow.
>>
>>
>>
>> Any of you have gone thru similar issues when migrated to Oracle
>> environment? What steps have you taken to improve the response? We have
>> a huge Cobol routine that runs off of our quick screens using DO
>> EXTERNAL. That is also taking longer time and a big concern to our
>> clients.
>>
>>
>>
>> Please let me know if you have any suggestions or information.
>>
>>
>>
>> Thanks very much
>>
>> Sagar Goparaju
>> TradePoint Systems
>>
>>
>>
>> STATEMENT OF CONFIDENTIALITY:
>> The information contained in this electronic message is intended for
>> the exclusive use of the addressee(s) and may contain confidential
>> information. If you are not the intended recipient of this email, be
>> advised you have received this message in error and that any use,
>> dissemination, forwarding, printing, or copying is strictly prohibited.
>> Please notify TradePoint Systems LLC immediately at (603) 889-3200 and
>> destroy all copies of this message and any attachments.
>>
>> This message may contain privileged and/or confidential
>> information. If you have received this e-mail in error or are not the
>> intended recipient, you may not use, copy, disseminate or distribute it;
>> do not open any attachments, delete it immediately from your system and
>> notify the sender promptly by e-mail that you have done so. Thank you.
>>
>>
>>
>> --
>> = = = = = = = = = = = = = = = = = = = = = = = = = = = =
>> Mailing list: powerh-l at lists.sowder.com
>> Subscribe: "subscribe" in message body to
>> powerh-l-request at lists.sowder.com
>> Unsubscribe: "unsubscribe <password>" in message body to
>> powerh-l-request at lists.sowder.com
>> http://lists.sowder.com/mailman/listinfo/powerh-l
>> This list is closed, thus to post to the list you must be a subscriber.
>>
>>
>> --
>> = = = = = = = = = = = = = = = = = = = = = = = = = = = =
>> Mailing list: powerh-l at lists.sowder.com
>> Subscribe: "subscribe" in message body to
>> powerh-l-request at lists.sowder.com
>> Unsubscribe: "unsubscribe <password>" in message body to
>> powerh-l-request at lists.sowder.com
>> http://lists.sowder.com/mailman/listinfo/powerh-l
>> This list is closed, thus to post to the list you must be a subscriber.
>>
>> --
>> = = = = = = = = = = = = = = = = = = = = = = = = = = = =
>> Mailing list: powerh-l at lists.sowder.com
>> Subscribe: "subscribe" in message body to
>> powerh-l-request at lists.sowder.com
>> Unsubscribe: "unsubscribe <password>" in message body to
>> powerh-l-request at lists.sowder.com
>> http://lists.sowder.com/mailman/listinfo/powerh-l
>> This list is closed, thus to post to the list you must be a subscriber.
>>
>> --
>> = = = = = = = = = = = = = = = = = = = = = = = = = = = =
>> Mailing list: powerh-l at lists.sowder.com
>> Subscribe: "subscribe" in message body to
>> powerh-l-request at lists.sowder.com
>> Unsubscribe: "unsubscribe <password>" in message body to
>> powerh-l-request at lists.sowder.com
>> http://lists.sowder.com/mailman/listinfo/powerh-l
>> This list is closed, thus to post to the list you must be a subscriber.
>>
>> --
>> = = = = = = = = = = = = = = = = = = = = = = = = = = = =
>> Mailing list: powerh-l at lists.sowder.com
>> Subscribe: "subscribe" in message body to
>> powerh-l-request at lists.sowder.com
>> Unsubscribe: "unsubscribe <password>" in message body to
>> powerh-l-request at lists.sowder.com
>> http://lists.sowder.com/mailman/listinfo/powerh-l
>> This list is closed, thus to post to the list you must be a subscriber.
>>
>>
>> --
>> = = = = = = = = = = = = = = = = = = = = = = = = = = = =
>> Mailing list: powerh-l at lists.sowder.com
>> Subscribe: "subscribe" in message body to
>> powerh-l-request at lists.sowder.com
>> Unsubscribe: "unsubscribe <password>" in message body to
>> powerh-l-request at lists.sowder.com
>> http://lists.sowder.com/mailman/listinfo/powerh-l
>> This list is closed, thus to post to the list you must be a subscriber.
>>
>
>
More information about the powerh-l
mailing list