Improves - what you need?

Posted in 2014

Summary

A call to vote on outstanding IBM Informix RFEs (SNMP, temp-space SQL interface, backups from secondaries, regex in MATCHES, in-memory temp tables, etc.), listing which were rejected and which were still under consideration. Discussion focused on the "CREATE OR REPLACE procedure" request: the originator said it can be emulated with BEGIN WORK; DROP PROCEDURE IF EXISTS; CREATE PROCEDURE; COMMIT, and testing showed waiting sessions pick up the new procedure (helped by AUTO_REPREPARE and a sensible LOCK WAIT). The caveat is non-logged databases, where no transaction is possible. Other wishes (e.g. DROP PROCEDURE ALL) got no resolution.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: High Availability & Replication, Backup & Restore, Storage & Space Management, Stored Procedures & SPL, Server Administration, Triggers, Constraints & Referential Integrity, Networking & sqlhosts Configuration, Migration, Import/Export & Data Conversion

Cesar Martins — Mon, 27 Oct 2014 18:37:48 — source: IIUG Forums & Mailing Lists

Hi ! Few weeks ago I received some updates from IBM RFE (Request for Enhancement) where some requests was rejected ... Features where I consider very important and huge missing at our beloved Informix... (some of this request are mine :) So , I wrote below a list of request what I consider important for a lot of Informix users/DBAs/clients... (me included) If you are interesting in something , please vote on it! (either at rejected request) If you have suggestion to make a request better , write your comments into the request RFE page! ============================================= Rejected : ID: 35921 - improve/update SNMP service - http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=35921 ID: 43877 - SQL interface to obtain the temporary space usage (tables, hash, sorts...) - http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=43877 ID: 34551 - EXPLAIN on windows - http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=34551 ID: 45728 - Backup from RSS or HDR Secondaries using ontape, onunload, onbar, dbexport - http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=45728 ============================================= Under Consideration or Submitted : ID: 61140 - SQLHOSTS groups connect to non-primary - http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=61140 ID: 61139 - Refresh for SSL Listeners (reload certificates) - http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=61139 ID: 33928 - Add SQL Function to create MD5 hashes (Oracle has GET_HASH_VALUE function) - http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=33928 ID: 34762 - Ability to re-create views and procedures without dependent objects being dropped - http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=34762 ID: 35529 - Alter table that has foreign keys - http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=35529 ID: 35917 - Allow TRUNCATE on table with users access - http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=35917 ID: 36229 - Implement CREATE OR REPLACE option for stored procedures - http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=36229 ID: 36245 - SQL to identify TEMP tables from current session - http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=36245 ID: 41329 - Implementation of regular expressions (adding to LIKE/MATCHES functions) - http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=41329 ID: 42071 - Encrypt source code for procedure/function - http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=42071 ID: 43684 - Compatibility to MySQL SQL Syntax - Omit unnecessary FROM Clause - http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=43684 ID: 45059 - set environment dbspacetemp - http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=45059 ID: 54307 - Only in-memory Temp table - http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=54307 Rejected: IBM has evaluated the request, and has determined that it can not be implemented at this time or does not align with the current multi-year strategy. This request may be resubmitted for consideration after 18 months from the date of submission. The request submitter will automatically be notified by email when the request qualifies for reconsideration and resubmission. --001a1133d75079f4d105066bd356

↪ replying to Cesar Martins

BENJAMIN THOMPSON — Tue, 28 Oct 2014 17:17:40 — source: IIUG Forums & Mailing Lists

ID: 36229 - Implement CREATE OR REPLACE option for stored procedures - http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=36229 This is one of mine but I am going to let it be closed because it has been pointed out to me that you can achieve this with: BEGIN WORK; DROP PROCEDURE IF EXISTS...CREATE PROCEDURE... COMMIT WORK; Obvious really :) It helps if sessions using this procedure have a sensible LOCK WAIT set to avoid lock timeouts. Ben.

↪ replying to BENJAMIN THOMPSON

Paul Watson — Tue, 28 Oct 2014 17:26:45 — source: IIUG Forums & Mailing Lists

I have always wanted a DROP PROCEDURE ALL sp_myspl So it would take out all the SPL name sp_myspl regardless of the number/type of input parameters Cheers Paul; > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > BENJAMIN THOMPSON > Sent: Tuesday, October 28, 2014 12:18 PM > To: ids@iiug.org > Subject: Re: Improves - what you need? [34045] > > ID: 36229 - Implement CREATE OR REPLACE option for stored procedures - > http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR > _ID=36229 > > This is one of mine but I am going to let it be closed because it has been > pointed out to me that you can achieve this with: > > BEGIN WORK; > DROP PROCEDURE IF EXISTS...> CREATE PROCEDURE... > COMMIT WORK; > > Obvious really :) > > It helps if sessions using this procedure have a sensible LOCK WAIT set to > avoid lock timeouts. > > Ben. > > > ********************************************************** > ********************* > Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to BENJAMIN THOMPSON

Fernando Nunes — Tue, 28 Oct 2014 18:55:44 — source: IIUG Forums & Mailing Lists

It may be obvious... But I have some doubts... Each session caches the existing procedure.... Not sure what happens between the DROP and the COMMIT... Have you tried it? On Tue, Oct 28, 2014 at 5:17 PM, BENJAMIN THOMPSON < benjamin.thompson@bskyb.com> wrote: > ID: 36229 - Implement CREATE OR REPLACE option for stored procedures - > http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=36229 > > This is one of mine but I am going to let it be closed because it has been > pointed out to me that you can achieve this with: > > BEGIN WORK; > DROP PROCEDURE IF EXISTS...> CREATE PROCEDURE... > COMMIT WORK; > > Obvious really :) > > It helps if sessions using this procedure have a sensible LOCK WAIT set to > avoid lock timeouts. > > Ben. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a113ee16e7fe6de0506803174

↪ replying to Paul Watson

Fernando Nunes — Tue, 28 Oct 2014 18:59:42 — source: IIUG Forums & Mailing Lists

From a user friendliness point of view... yes. But when we think R&D resources are never enough... I would give is a very low priority. Again, one for that category "Nice, why not... but would I prefer this over a lot of ones not being considered? NO" :) Regards On Tue, Oct 28, 2014 at 5:26 PM, Paul Watson <paul@oninit.com> wrote: > I have always wanted a > > DROP PROCEDURE ALL sp_myspl> > So it would take out all the SPL name sp_myspl regardless of the > number/type > of input parameters > > Cheers > Paul; > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > > BENJAMIN THOMPSON > > Sent: Tuesday, October 28, 2014 12:18 PM > > To: ids@iiug.org > > Subject: Re: Improves - what you need? [34045] > > > > ID: 36229 - Implement CREATE OR REPLACE option for stored procedures - > > http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR > > _ID=36229 > > > > This is one of mine but I am going to let it be closed because it has > been > > pointed out to me that you can achieve this with: > > > > BEGIN WORK; > > DROP PROCEDURE IF EXISTS...> > CREATE PROCEDURE... > > COMMIT WORK; > > > > Obvious really :) > > > > It helps if sessions using this procedure have a sensible LOCK WAIT set > to > > avoid lock timeouts. > > > > Ben. > > > > > > ********************************************************** > > ********************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a113f1a8cb68cda0506803fe6

↪ replying to Fernando Nunes

Paul Watson — Tue, 28 Oct 2014 19:04:29 — source: IIUG Forums & Mailing Lists

I always told that Drop procedure all assign Would be a bad thing :) Cheers Paul > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Fernando Nunes > Sent: Tuesday, October 28, 2014 2:00 PM > To: ids@iiug.org > Subject: Re: Improves - what you need? [34049] > > >From a user friendliness point of view... yes. But when we think R&D > resources are never enough... I would give is a very low priority. > Again, one for that category "Nice, why not... but would I prefer this over > a lot of ones not being considered? NO" :) > > Regards > > On Tue, Oct 28, 2014 at 5:26 PM, Paul Watson <paul@oninit.com> wrote: > > > I have always wanted a > > > > DROP PROCEDURE ALL sp_myspl> > > > So it would take out all the SPL name sp_myspl regardless of the > > number/type > > of input parameters > > > > Cheers > > Paul; > > > > > -----Original Message----- > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > > > BENJAMIN THOMPSON > > > Sent: Tuesday, October 28, 2014 12:18 PM > > > To: ids@iiug.org > > > Subject: Re: Improves - what you need? [34045] > > > > > > ID: 36229 - Implement CREATE OR REPLACE option for stored procedures - > > > > http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR > > > _ID=36229 > > > > > > This is one of mine but I am going to let it be closed because it has > > been > > > pointed out to me that you can achieve this with: > > > > > > BEGIN WORK; > > > DROP PROCEDURE IF EXISTS...> > > CREATE PROCEDURE... > > > COMMIT WORK; > > > > > > Obvious really :) > > > > > > It helps if sessions using this procedure have a sensible LOCK WAIT set > > to > > > avoid lock timeouts. > > > > > > Ben. > > > > > > > > > > ********************************************************** > > > ********************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > ********************************************************** > ********************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --001a113f1a8cb68cda0506803fe6 > > > ********************************************************** > ********************* > Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Fernando Nunes

Fernando Nunes — Tue, 28 Oct 2014 19:04:51 — source: IIUG Forums & Mailing Lists

Yes... it works.... so it may be an issue for non-logged databases only.... On Tue, Oct 28, 2014 at 6:55 PM, Fernando Nunes <domusonline@gmail.com> wrote: > It may be obvious... But I have some doubts... Each session caches the > existing procedure.... Not sure what happens between the DROP and the > COMMIT... > Have you tried it? > > On Tue, Oct 28, 2014 at 5:17 PM, BENJAMIN THOMPSON < > benjamin.thompson@bskyb.com> wrote: > > > ID: 36229 - Implement CREATE OR REPLACE option for stored procedures - > > > http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=36229 > > > > This is one of mine but I am going to let it be closed because it has > been > > pointed out to me that you can achieve this with: > > > > BEGIN WORK; > > DROP PROCEDURE IF EXISTS...> > CREATE PROCEDURE... > > COMMIT WORK; > > > > Obvious really :) > > > > It helps if sessions using this procedure have a sensible LOCK WAIT set > to > > avoid lock timeouts. > > > > Ben. > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --001a113ee16e7fe6de0506803174 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --20cf303bfefe3156d7050680518c

↪ replying to Fernando Nunes

BENJAMIN THOMPSON — Wed, 29 Oct 2014 11:11:47 — source: IIUG Forums & Mailing Lists

I think it works even for sessions with open statement handles if AUTO_REPREPARE is turned on and works on your system (it is buggy in some early 11.70 releases). I have previously tested this type of thing in some depth, I just didn't look at multiple DDL statements in a single transaction. My quick test yesterday was to drop the procedure inside a transaction. I can then set up a second session that wants to call that same procedure that waits on locks. If I then create the procedure in the first session and commit, the second session executes the procedure correctly. In a non-logged database I guess the problem is that you can't execute "begin work" so a single "create or replace" function might help there. Ben.

↪ replying to BENJAMIN THOMPSON

Fernando Nunes — Wed, 29 Oct 2014 11:18:02 — source: IIUG Forums & Mailing Lists

Yes. Regarding non-logged databases I think nothing can help them :) The problem I see for other usages (triggers for example) is the difficulty in being able to change an object structure... IFX_DIRTY_WAIT, DML lock vs DDL lock and so on... But this is definitely something to keep in mind when changing procedures. And as you mentioned, it requires the sessions are not in LOCK MODE NOT WAIT.... Regards On Wed, Oct 29, 2014 at 11:11 AM, BENJAMIN THOMPSON < benjamin.thompson@bskyb.com> wrote: > I think it works even for sessions with open statement handles if > AUTO_REPREPARE is turned on and works on your system (it is buggy in some > early 11.70 releases). I have previously tested this type of thing in some > depth, I just didn't look at multiple DDL statements in a single > transaction. > > My quick test yesterday was to drop the procedure inside a transaction. I > can > then set up a second session that wants to call that same procedure that > waits > on locks. If I then create the procedure in the first session and commit, > the > second session executes the procedure correctly. > > In a non-logged database I guess the problem is that you can't execute > "begin > work" so a single "create or replace" function might help there. > > Ben. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --089e013c6a7e7f512705068deab4

Improves - what you need?

Glossary

Your privacy choices