getting system time

People, I have a question, our develope tend to use select first 1 current from systables; to be able to read the database server date. Is the normal way to create a dummy table so that the developes can run select current from tb_dummy; or there is a better way to do it. Regards Kenneth

The usual Informix paradigm is to: SELECT CURRENT from systables WHERE tabid = 1; There is ALWAYS a tabid == 1 which is systables itself and that will tend to be quicker than SELECT FIRST 1 FROM systables; unless you are running with SET OPTIMIZATION FIRST_ROWS; in which case it probably doesn't matter, so why not just use the 'WHERE tabid = 1' which is always fast. Oracle has a table with a single element in it that's a standard part of every database and is used for queries like this, you can do that or just use systables, no real difference. Note, though, I know of one shop that used the dummy table method and that worked fine until one day their 3rd party software vendor was installing its new software version with schema changes. The tech found this non-standard table with a single column with only one row in it which contained a NULL value. It wasn't in the list of subsidiary tables the client has told him they needed fo their applications, so, being diligent he dropped the odd, unneeded, and obviously useless thing. The tech brought the system back online, ran the vendor's standard tests and declared the system ready for prime time and went home. When the shop started their own apps to interface with the system BOOM! I think it took them most of the day to figure out why nothing was working! Word to the wise. Art S. Kagel ----- Original Message ----- From: Kenneth Penza <kenneth.penza@gov.mt> At: 1/27 10:48 > People, > > I have a question, our develope tend to use select first 1 current from > systables; to be able to read the database server date. Is the normal way to > create a dummy table so that the developes can run select current from tb_dummy; > or there is a better way to do it. > > Regards > Kenneth

Create a VIEW for the developers....It keeps them off of the sys tables, hides what you are doing and SECURE (permissions, etc.) -----Original Message----- From: KENNETH PENZA [mailto:kenneth.penza@gov.mt] Sent: Thursday, January 27, 2005 10:16 AM To: ids@iiug.org Subject: getting system time [4107] People, I have a question, our develope tend to use select first 1 current from systables; to be able to read the database server date. Is the normal way to create a dummy table so that the developes can run select current from tb_dummy; or there is a better way to do it. Regards Kenneth Please do not transmit orders or instructions regarding a UBS account by email. The information provided in this email or any attachments is not an official transaction confirmation or account statement. For your protection, do not include account numbers, Social Security numbers, credit card numbers, passwords or other non-public information in your email. Because the information contained in this message may be privileged, confidential, proprietary or otherwise protected from disclosure, please notify us immediately by replying to this message and deleting it from your computer if you have received this communication in error. Thank you. UBS Financial Services Inc. UBS International Inc.

Or, my preferred option, is a stored procedure: I typically have a procedure that returns the USER and CURRENT year to second; this can be used by the developers for the system time or by triggers for record auditing... -----Original Message----- From: Zablatzky, .... [mailto:Wayne.Zablatzky@ubs.com] Sent: Friday, 28 January 2005 11:53 AM To: ids@iiug.org Subject: RE: getting system time [4113] Create a VIEW for the developers....It keeps them off of the sys tables, hides what you are doing and SECURE (permissions, etc.) -----Original Message----- From: KENNETH PENZA [mailto:kenneth.penza@gov.mt] Sent: Thursday, January 27, 2005 10:16 AM To: ids@iiug.org Subject: getting system time [4107] People, I have a question, our develope tend to use select first 1 current from systables; to be able to read the database server date. Is the normal way to create a dummy table so that the developes can run select current from tb_dummy; or there is a better way to do it. Regards Kenneth Please do not transmit orders or instructions regarding a UBS account by email. The information provided in this email or any attachments is not an official transaction confirmation or account statement. For your protection, do not include account numbers, Social Security numbers, credit card numbers, passwords or other non-public information in your email. Because the information contained in this message may be privileged, confidential, proprietary or otherwise protected from disclosure, please notify us immediately by replying to this message and deleting it from your computer if you have received this communication in error. Thank you. UBS Financial Services Inc. UBS International Inc. ---------------------------------------------------- This message is for the named person's use only. Privileged/confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message, and notify us immediately. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorised to state them to be the views of any such entity. ----------------------------------------------------

I like this one, call it cool|esoteric: select current from table(set{1}); No need of any table nor view nut it won't work on older versions of the engine. J. PD: For development purposes, nothing better than select current from dual; It is very simple to write. -----Original Message----- From: "ART KAGEL, ...." <KAGEL@bloomberg.net> To: ids@iiug.org Date: Thu, 27 Jan 2005 11:05:05 -0500 (EST) Subject: Re: getting system time [4109] The usual Informix paradigm is to: SELECT CURRENT from systables WHERE tabid = 1; There is ALWAYS a tabid == 1 which is systables itself and that will tend to be quicker than SELECT FIRST 1 FROM systables; unless you are running with SET OPTIMIZATION FIRST_ROWS; in which case it probably doesn't matter, so why not just use the 'WHERE tabid = 1' which is always fast. Oracle has a table with a single element in it that's a standard part of every database and is used for queries like this, you can do that or just use systables, no real difference. Note, though, I know of one shop that used the dummy table method and that worked fine until one day their 3rd party software vendor was installing its new software version with schema changes. The tech found this non-standard table with a single column with only one row in it which contained a NULL value. It wasn't in the list of subsidiary tables the client has told him they needed fo their applications, so, being diligent he dropped the odd, unneeded, and obviously useless thing. The tech brought the system back online, ran the vendor's standard tests and declared the system ready for prime time and went home. When the shop started their own apps to interface with the system BOOM! I think it took them most of the day to figure out why nothing was working! Word to the wise. Art S. Kagel ----- Original Message ----- From: Kenneth Penza <kenneth.penza@gov.mt> At: 1/27 10:48 > People, > > I have a question, our develope tend to use select first 1 current from > systables; to be able to read the database server date. Is the normal way to > create a dummy table so that the developes can run select current from tb_dummy; > or there is a better way to do it. > > Regards > Kenneth Jean Sagi jeansagi@myrealbox.com jeansagi@yahoo.com