Can't construct query plan in ASE 12.0 - but can in ASE 12.0.0.6
Error 325 is being returned when trying to execute a stored procedure on
version :
Adaptive Server Enterprise/12.0/P/SWR 8773 ESD 1/RS6000/AIX
4.3.2/1580/64bit/FBO/Tue Dec 7 03:29:32 1999 ...
this is the code causing the error when running on version listed above...
it's a sub-query within a stored proc
AND SBSB.HIST_ROW_ID = (select
MAX(SBSB2.HIST_ROW_ID)
FROM
Massive_db.audit.CMC_SBSB_SUBSC SBSB2
WHERE
SBSB2.SBSB_CK = SBSB.SBSB_CK
AND CONVERT(CHAR(10),SBEL.SBEL_INSQ_DT,101) = CONVERT(CHAR
10),SBSB2.HIST_CREATE_DTM,101))
When executing same stored procedure on ASE version :
Adaptive Server Enterprise/12.0.0.6/P/EBF 10393 ROLLUP/RS6000/AIX
4.3.2/1891/64bit/FBO/Thu Aug 15 02:05:47 2002
The stored procedure executes without error on ASE 12.0.0.6. Apparently
something in ASE 12.0 doesn't allow for a traversal back up the tree in a
sub query with combined SARGS?
Does the reason for the error (can't construct a query plan) have to do
with the additional search argument (AND SARG on SBEL). With an additional
field from a table within the main query and the fact that there is a
conversion taking place, I think this essentially invalidates the argument
AND CONVERT(CHAR(10),SBEL.SBEL_INSQ_DT,101) = CONVERT(CHAR
10),SBSB2.HIST_CREATE_DTM,101)) and invalidates the SARG. For some reason
the optimizer is unable to use statistics to combine the selectivity of the
search arguments with this additional argument. But it works in ASE version
12.0.06.
The workaround so far on ASE 12.0 has been to construct the sub query in
this way:
AND SBSB.HIST_ROW_ID = (SELECT
MAX(SBSB2.HIST_ROW_ID)
FROM
Massive_db.audit.CMC_SBSB_SUBSC SBSB2,
Massive_db.dbo.CMC_SBSB_SUBSC SBSB1,
Massive_db.dbo.CMC_SBEL_ELIG_ENT SBEL1
WHERE
SBSB2.SBSB_CK
= SBSB1.SBSB_CK
AND SBSB2.SBSB_CK =
SBEL1.SBSB_CK
AND SBSB1.SBSB_CK =
SBEL1.SBSB_CK
AND
CONVERT(CHAR(10),SBEL1.SBEL_INSQ_DT,101) =
CONVERT(CHAR(10),SBSB2.HIST_CREATE_DTM,101))
Is there something in EBF 10393 ROLLUP that addresses this?
thx!
Nick Johnston
|
0 |
|
Nick
11/4/2003 2:33:07 PM
sybase.ase.general
8655 articles. 
0 followers.
0 Replies
735 Views
Similar Artilces:
ASE 12.0.0.0 / SWR 8775 UPGRADE TO ASE 12.0.0.6/EBF 10628 PERFORMANCE PROBLEM
Hi All, We recently upgraded ASE 12.0.0.0/SWR 8775 to ASE 12.0.0.6/EBF 10628. Some queries stopped returning resultsets. Recompiled the stored procs which fixed the issue. But the queries are running slower now. The overall performance of the app has become sluggish. The CPU is generally running higher as well! Have also set LD_LIBRARY_PATH_64 to include the new Solaris 8 threads library. Anybody any insights into this? How can we get back to the pre-upgrade performance levels short of rolling back the EBF. Regards Perry Perry, I guess 12.0.0.6 requires some Solaris ...
ASE 12.0.0.6 to ASE 12.5 and Rep Server 12.1
We are planning the upgrade of our ASE 12.0.0.6 64bit on AIX 4.3.3 to ASE 12.5 64bit on AIX 4.3.3. We are also using Rep Server 12.1 with these two servers. My question is do I need to upgrade Rep Server as well, or will 12.1 Rep Server Work with ASE 12.5. Also, we will be upgrading AIX to 5.1 as well. Any advice is appreciated. Thanks, Troy It will work, however you wont be able to use any of the 12.5 new datatypes, and the rep_agents will spit out annoying error/warning messages everytime they are restarted. Our production system has been running that way for about ye...
Upgrade ASE 12.0.0.4 to 12.0.0.8
I upgraded my ASE to version 12.0.0.8. Now one of the application get a permanent error: "Not enough procedure cache". I increased the proc cache to 40% (total memory: 1 GB) - no success. When I rolled back to 12.0.0.4 everything works fine (with less procedure cache). The ASE is running on Solaris 7. The application connects thru the Merant ODBC driver. I gave the application already back to development to check. Any help how this can be avoided by a change on the host/server is appreciated. Bernd I always recommend dropping/recreating all of the stored...
Migrating from ASE 12.0 32-bit to ASE 12.0 64-bit
Has anyone run into any problems migrating from 32-bit to 64-bit? ...
Migrating from ASE 12.0 32-bit to ASE 12.0 64-bit
Has anyone run into any problems migrating from 32-bit to 64-bit? ...
Can I go back to ASE 12.5.0.2IR from 12.5.0.3 ?
I'm having some new problems with ASE 12.5.0.3 which I never had before. I've already ran the installmaster and installmsg scripts for 12.5.0.3. Can I restore the 12.5.0.2IR files and go (after a database dump of course) ? Should I re-run the 12.5.0.2IR installmaster and installmsg scripts ? Thanks in advance. This is a multi-part message in MIME format. --------------CA9B04E03DABBC44D94242CC Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Wally wrote: > > I'm having some new problems with ASE 12.5.0.3 which I never had before...
Can datadevices be switched between ASE 12.5.0.2 and 12.5.0.3 ESD4 and back ?
I'm short on disk space and need to see whether 12.5.0.3 ESD#4 fixes a problem I'm seeing with a large db that I'm presently running completely off file based devices. I'd like to try the following, but am not sure whether doing so will really make a mess of things - has anyone done this or something similar: 1. shutdown 12.5.0.2 server 2. copy the 12.5.0.2 servers master and sybsystemprocs device files to somewhere safe (they're relatively small). 3. start the 12.5.0.3 ESD4 server s/w using the same device files used with the 12.5.0.2 server 4. I'm a...
any issues to restore dump file of ASE 12.0 (32bit) to a ASE 12.0 (64bit) system ?
Hi, Does anyone know whether there is an issue that we restore the dump file (not master and system db) of ASE 12.0 (32bit) to a ASE 12.0 (64bit) system ? Assume that both system DBs are the same. Cheers paul > Hi, > > Does anyone know whether there is an issue that we restore > the dump file (not master and system db) of ASE 12.0 > (32bit) to a ASE 12.0 (64bit) system ? Assume that both > system DBs are the same. > > Cheers > paul If you have a recent ebf of ASE 12.0 (say 12.0.0.4 or later) than no problem. Earlier ebfs had a few problems....
why I can't install ase 12.0
today I install sybase ase 12.0 on dec alpha 1000,os is dec unix 5.1.when I start the server,the errorlog reports: 00:00000:00000:2001/02/16 14:36:40.45 kernel Using config area from primary master device. 00:00000:00000:2001/02/16 14:36:40.48 kernel Configuration Error: Configuration file, '/usr/users/syb12/SYB12_TEST.cfg', does not exist. 00:00000:00000:2001/02/16 14:36:40.56 kernel Warning: A configuration file was not specified and the default file '/usr/users/syb12/SYB12_TEST.cfg' does not exist. SQL Server creates the default file with the default configurat...
why I can't install ase 12.0 #2
today I install sybase ase 12.0 on dec alpha 1000,os is dec unix 5.1.when I start the server,the errorlog reports: 00:00000:00000:2001/02/16 14:36:40.45 kernel Using config area from primary master device. 00:00000:00000:2001/02/16 14:36:40.48 kernel Configuration Error: Configuration file, '/usr/users/syb12/SYB12_TEST.cfg', does not exist. 00:00000:00000:2001/02/16 14:36:40.56 kernel Warning: A configuration file was not specified and the default file '/usr/users/syb12/SYB12_TEST.cfg' does not exist. SQL Server creates the default file with the default configurat...
Can't Install Chinese Charset on ASE 12.0
I have installed ASE 12.0.0.4 ESD4 on Chinese Windows 2000 Advanced Server(SP2),I run Sybase Server Config to install chinese language and eucgb charset and set both as default, but it executes unsuccessfully, It pops up an error window showing that "Task failed:install a language(s),Terminating configuraion." Who can help me? Thanks in avance. In Chinese Environment,which charsets is best suitable? iso_1,cp850,utf8 or eucgb? Who can give me some suggestion. tangxucheng@263.net wrote... > I have installed ASE 12.0.0.4 ESD4 on Chinese Windows 2000 Advanced > Server...
Upgrading from ASE (32-bit) 12.0.0.7 on Solaris 2.8 64-bit to ASE(64-bit) 12.5.1 on Solaris 64-bit
Hello all, We are planning to upgrade our ASE servers from a 32-bit ASE 12.0.0.7 to 64-bit ASE 12.5.1 on Solaris 8. Could you please give what are the steps to be taken care and how do I start with ( Do I have to first upgrade to 32-bit12.5.1 and then upgrade to 64-bit ASE 12.5.1, or directly upgrade from 32-bit 12.0.0.7 to 64-bit 12.5.1 ?). Could you also please point to some documentation (about upgrading 32-bit 12.0.0.7 to 64-bit 12.5.1). Thanks RK ...
Can't start isql after fresh install of ASE 12.0 on NT
I am not able to start isql after a fresh install of ASE 12.0 on NT 4 SP6 machine. Does any one know why???? When I try to open it from E:\Sybase\OCS_12-01\isql' it opens a command window with password prompt and when I hit enter after the password it just disappears Kris wrote: ------------------------------------------------------------ When I try to open it from E:\Sybase\OCS_12-01\isql' it opens a command window with password prompt and when I hit enter after the password it just disappears ------------------------------------------------------------ Hi, Kris ...
SQLRemote Problem with 7.0.2-1 and 6.0.2-1 on Linux [ASE 11.9.2-3 and ASE 12.5 eval]
Hi all I got the following problems using sql-remote. on ASE 12.5 eval: I got the following RPMs installed on RedHat Linux 7.2 - Kernel 2.4.17 - glibc-2.2.4 sybase-ase-12.5-3.i386.rpm sybase-common-12.5-3.i386.rpm sybase-openclient-12.5-3.i386.rpm sybase-sqlremote-7.0.2-1.i386.rpm After the execution of ssxtract or ssremote I got an error like: ../ssxtract: /opt/sybase-12.5/SQLRemote/lib/libsstasks7_r.so: version `libdbtasks7_r.so' not found (required by ./ssxtract) The file libsstasks7_r.so exists and the path is correctly specified in /etc/ld.so.conf. There'...
Hi All, We recently upgraded ASE 12.0.0.0/SWR 8775 to ASE 12.0.0.6/EBF 10628. Some queries stopped returning resultsets. Recompiled the stored procs which fixed the issue. But the queries are running slower now. The overall performance of the app has become sluggish. The CPU is generally running higher as well! Have also set LD_LIBRARY_PATH_64 to include the new Solaris 8 threads library. Anybody any insights into this? How can we get back to the pre-upgrade performance levels short of rolling back the EBF. Regards Perry Perry, I guess 12.0.0.6 requires some Solaris ...
ASE 12.0.0.6 to ASE 12.5 and Rep Server 12.1
We are planning the upgrade of our ASE 12.0.0.6 64bit on AIX 4.3.3 to ASE 12.5 64bit on AIX 4.3.3. We are also using Rep Server 12.1 with these two servers. My question is do I need to upgrade Rep Server as well, or will 12.1 Rep Server Work with ASE 12.5. Also, we will be upgrading AIX to 5.1 as well. Any advice is appreciated. Thanks, Troy It will work, however you wont be able to use any of the 12.5 new datatypes, and the rep_agents will spit out annoying error/warning messages everytime they are restarted. Our production system has been running that way for about ye...
Upgrade ASE 12.0.0.4 to 12.0.0.8
I upgraded my ASE to version 12.0.0.8. Now one of the application get a permanent error: "Not enough procedure cache". I increased the proc cache to 40% (total memory: 1 GB) - no success. When I rolled back to 12.0.0.4 everything works fine (with less procedure cache). The ASE is running on Solaris 7. The application connects thru the Merant ODBC driver. I gave the application already back to development to check. Any help how this can be avoided by a change on the host/server is appreciated. Bernd I always recommend dropping/recreating all of the stored...
Migrating from ASE 12.0 32-bit to ASE 12.0 64-bit
Has anyone run into any problems migrating from 32-bit to 64-bit? ...
Migrating from ASE 12.0 32-bit to ASE 12.0 64-bit
Has anyone run into any problems migrating from 32-bit to 64-bit? ...
Can I go back to ASE 12.5.0.2IR from 12.5.0.3 ?
I'm having some new problems with ASE 12.5.0.3 which I never had before. I've already ran the installmaster and installmsg scripts for 12.5.0.3. Can I restore the 12.5.0.2IR files and go (after a database dump of course) ? Should I re-run the 12.5.0.2IR installmaster and installmsg scripts ? Thanks in advance. This is a multi-part message in MIME format. --------------CA9B04E03DABBC44D94242CC Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Wally wrote: > > I'm having some new problems with ASE 12.5.0.3 which I never had before...
Can datadevices be switched between ASE 12.5.0.2 and 12.5.0.3 ESD4 and back ?
I'm short on disk space and need to see whether 12.5.0.3 ESD#4 fixes a problem I'm seeing with a large db that I'm presently running completely off file based devices. I'd like to try the following, but am not sure whether doing so will really make a mess of things - has anyone done this or something similar: 1. shutdown 12.5.0.2 server 2. copy the 12.5.0.2 servers master and sybsystemprocs device files to somewhere safe (they're relatively small). 3. start the 12.5.0.3 ESD4 server s/w using the same device files used with the 12.5.0.2 server 4. I'm a...
any issues to restore dump file of ASE 12.0 (32bit) to a ASE 12.0 (64bit) system ?
Hi, Does anyone know whether there is an issue that we restore the dump file (not master and system db) of ASE 12.0 (32bit) to a ASE 12.0 (64bit) system ? Assume that both system DBs are the same. Cheers paul > Hi, > > Does anyone know whether there is an issue that we restore > the dump file (not master and system db) of ASE 12.0 > (32bit) to a ASE 12.0 (64bit) system ? Assume that both > system DBs are the same. > > Cheers > paul If you have a recent ebf of ASE 12.0 (say 12.0.0.4 or later) than no problem. Earlier ebfs had a few problems....
why I can't install ase 12.0
today I install sybase ase 12.0 on dec alpha 1000,os is dec unix 5.1.when I start the server,the errorlog reports: 00:00000:00000:2001/02/16 14:36:40.45 kernel Using config area from primary master device. 00:00000:00000:2001/02/16 14:36:40.48 kernel Configuration Error: Configuration file, '/usr/users/syb12/SYB12_TEST.cfg', does not exist. 00:00000:00000:2001/02/16 14:36:40.56 kernel Warning: A configuration file was not specified and the default file '/usr/users/syb12/SYB12_TEST.cfg' does not exist. SQL Server creates the default file with the default configurat...
why I can't install ase 12.0 #2
today I install sybase ase 12.0 on dec alpha 1000,os is dec unix 5.1.when I start the server,the errorlog reports: 00:00000:00000:2001/02/16 14:36:40.45 kernel Using config area from primary master device. 00:00000:00000:2001/02/16 14:36:40.48 kernel Configuration Error: Configuration file, '/usr/users/syb12/SYB12_TEST.cfg', does not exist. 00:00000:00000:2001/02/16 14:36:40.56 kernel Warning: A configuration file was not specified and the default file '/usr/users/syb12/SYB12_TEST.cfg' does not exist. SQL Server creates the default file with the default configurat...
Can't Install Chinese Charset on ASE 12.0
I have installed ASE 12.0.0.4 ESD4 on Chinese Windows 2000 Advanced Server(SP2),I run Sybase Server Config to install chinese language and eucgb charset and set both as default, but it executes unsuccessfully, It pops up an error window showing that "Task failed:install a language(s),Terminating configuraion." Who can help me? Thanks in avance. In Chinese Environment,which charsets is best suitable? iso_1,cp850,utf8 or eucgb? Who can give me some suggestion. tangxucheng@263.net wrote... > I have installed ASE 12.0.0.4 ESD4 on Chinese Windows 2000 Advanced > Server...
Upgrading from ASE (32-bit) 12.0.0.7 on Solaris 2.8 64-bit to ASE(64-bit) 12.5.1 on Solaris 64-bit
Hello all, We are planning to upgrade our ASE servers from a 32-bit ASE 12.0.0.7 to 64-bit ASE 12.5.1 on Solaris 8. Could you please give what are the steps to be taken care and how do I start with ( Do I have to first upgrade to 32-bit12.5.1 and then upgrade to 64-bit ASE 12.5.1, or directly upgrade from 32-bit 12.0.0.7 to 64-bit 12.5.1 ?). Could you also please point to some documentation (about upgrading 32-bit 12.0.0.7 to 64-bit 12.5.1). Thanks RK ...
Can't start isql after fresh install of ASE 12.0 on NT
I am not able to start isql after a fresh install of ASE 12.0 on NT 4 SP6 machine. Does any one know why???? When I try to open it from E:\Sybase\OCS_12-01\isql' it opens a command window with password prompt and when I hit enter after the password it just disappears Kris wrote: ------------------------------------------------------------ When I try to open it from E:\Sybase\OCS_12-01\isql' it opens a command window with password prompt and when I hit enter after the password it just disappears ------------------------------------------------------------ Hi, Kris ...
SQLRemote Problem with 7.0.2-1 and 6.0.2-1 on Linux [ASE 11.9.2-3 and ASE 12.5 eval]
Hi all I got the following problems using sql-remote. on ASE 12.5 eval: I got the following RPMs installed on RedHat Linux 7.2 - Kernel 2.4.17 - glibc-2.2.4 sybase-ase-12.5-3.i386.rpm sybase-common-12.5-3.i386.rpm sybase-openclient-12.5-3.i386.rpm sybase-sqlremote-7.0.2-1.i386.rpm After the execution of ssxtract or ssremote I got an error like: ../ssxtract: /opt/sybase-12.5/SQLRemote/lib/libsstasks7_r.so: version `libdbtasks7_r.so' not found (required by ./ssxtract) The file libsstasks7_r.so exists and the path is correctly specified in /etc/ld.so.conf. There'...
Web resources about - Can't construct query plan in ASE 12.0 - but can in ASE 12.0.0.6 - sybase.ase.generalResources last updated: 12/26/2015 2:28:58 AM