Powerhouse + Oracle-9i + AIX - Response issues
Deskin, Bob
Bob.Deskin at Cognos.COM
Thu Sep 1 12:49:07 CDT 2005
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.
More information about the powerh-l
mailing list