| Latest versions | fixlist |
| 15.0.xC1.2 |
 |
| 14.10.xC13 |
|
| 12.10.xC16.X5 |
|
| 11.70.xC9.XB |
|
| 11.50.xC9.X2 |
|
| 11.10.xC3.W5 |
|
| Have problems? - contact us. | |
| Register for free |
|
| Contact form |
|
Informix - Problem description
| Problem IT45509 | Status: Closed |
DBSCHEMA IS STUCK IN "WITH SYSPROCAUTH_CTE..." QUERY APPARENTLY TRYING TO DETERMINE THE STORED PROCEDURE PERMISSIONS STATEMENTS | |
| product: | |
INFORMIX SERVER / 5725A3900 / E10 - | |
| Problem description: | |
dbschema can hang in this query while attempting to find stored
procedure permissions:
Current SQL statement (5673) :
with sysprocauth_cte as( select d.procid, d.grantor,
d.grantee,
d.procauth, 0 AS hierarchy_level from informix.sysprocauth d
where
d.grantor = 'informix' OR d.grantor in (select username from
informix.sysusers where usertype = 'D') union all select
d.procid,
d.grantor, d.grantee, d.procauth, hierarchy_level+1 from
informix.sysprocauth d, sysprocauth_cte where d.grantor =
sysprocauth_cte.grantee ) select a.owner, a.procname,
sysprocauth_cte.procid, sysprocauth_cte.grantor,
sysprocauth_cte.grantee,
sysprocauth_cte.procauth from sysprocauth_cte join
informix.sysprocauth b
on sysprocauth_cte.procid = b.procid and
sysprocauth_cte.grantor =
b.grantor and sysprocauth_cte.grantee = b.grantee and
sysprocauth_cte.procauth = b.procauth join
informix.sysprocedures a on
sysprocauth_cte.procid = a.procid where (a.mode = 'O' OR
a.mode = 'D' OR
a.mode = 'R') order by sysprocauth_cte.procid,
sysprocauth_cte.hierarchy_level
The SQL can vary depending upon the exact dbschema options used.
The onstat -t command shows several _temptable and
SORTTEMP:informix.th_tmprun_* temporary tables. | |
| Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * Users of Informix Server prior to 14.10.xC11. * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to Informix Server 14.10.xC11. * **************************************************************** | |
| Local Fix: | |
This can be worked-around by using the dbschema binary from 14.10.xC9. | |
| Solution | |
| Workaround | |
**************************************************************** * USERS AFFECTED: * * Users of Informix Server prior to 14.10.xC11. * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to Informix Server 14.10.xC11. * **************************************************************** | |
| Comment | |
Fixed in Informix Server 14.10.xC11. | |
| Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 15.02.2024 12.06.2024 12.06.2024 |
| Problem solved at the following versions (IBM BugInfos) | |
| Problem solved according to the fixlist(s) of the following version(s) | |