The ISSUG Infobase contains archived postings from our on-line discussion forums. This index is for most of the 1998 postings. Nothing here may be quoted without the written permission of the author(s) of the original postings.
FAQ98 content: George Simpson
FAQ 98 - Section 7
#SUBJECT:
To Round or not to Round
#BY : Chris Uttley
Using COOL:GEN, when writing action diagrams, it often pops up asking you if you want to ROUND or NOT ROUND the results of your SET statement.
Outside of the times when you really need rounding, is it best to always say NOT ROUNDED???
Example
- Set IEF_SUPPLIED COUNT to 1
OR - Set OUT ENTITY1 ID to LCL ENTITY2 ID
My assumption is that I should always use NOT ROUNDED, but I would like to know if anyone has any concerns or knows of any bugs when doing this.
RE: To Round
or not to Round
#BY : Doug Michael
I think
you're right that NOT ROUNDED is appropriate in most cases. There was
a paper in the TI European Customer Support newsletter of August 93 (yes
93!) on this subject ("The use of ROUNDED in action diagram SET statements").
However I have no idea how you'd get a copy of this - the archives on
the Sterling Customer Support website only go back to 94.
#SUBJECT:
Accessing legacy data using large integers
#BY : Henrik
Has anyone tried accessing data created outside CoolGen from a CoolGen application, using large integers as key?
The data in the database contains integer fields which are 10 digits long, but when a 10 digit numeric field is defined to CoolGen, the host variable that is generated is COMP-3, which causes a tablespace scan when comparing to a DB2 integer field.
#SUBJECT:
Decimal Precision
#BY : Tineke Malant
#SUBJECT:
Oracle read decimals
#BY : Marie-Agnes Pilon
I have a Oracle read using Cool:Gen windows NT 4.0 Oracle 7.3, the decimal field is getting the following after the read :"-1.#IO". We have another read online that reads and displays the information with no problem. The program having the problem is a batch program.
We have checked the attribute definition in Cool:gen and Oracle. We have check the value on the table, which is 88.89. The field is defined as number of 11 with 3 decimals.
We have look at the code and nothing is strange there. They are no operations done on the field. We check the value using Trace.
I have opened a ticket with Sterling however I need some quick help because the program is to be promoted soon. The whole problem does not make any sense to me especially the fact that another read on the same table works fine.
#SUBJECT:
Defaulting a field to null
#BY : Jim Davis
Is there a way to default an optional numeric field to NULL in COOL:Gen?
RE: Defaulting
a field to null
#BY : george
Far as I know you can't get an online app to do so. Same trouble with spaces, it sticks a space in your varchar field.
RE: Defaulting
a field to null
#BY : Mathur
RE: Defaulting
a field to null
#BY : Keith Norris
A partial solution to this is to use the TD property
"Initialize unspecified optional fields to Null".
RE: Defaulting
a field to null
#BY : Glenn Smyth
IF Attribute
= zero/spaces ... Create SET attribute TO NULL
Else ... Create SET attribute TO import view attribute
This of course leads to exponential growth in create/update statements as you get more optional attributes. In order to be really useful you would need the ability to set local attrbute values to NULL (one for the enhancement request list).
RE: Defaulting
a field to null
#BY : Dave. Pember
We had the same problem. Our understanding is that NULL values can be checked and set in Entity views but when a NULL value is moved to an export, import or local view the NULL value becomes 0 or space - depending on the data type. There are some implications as if a NULL value is read from the database and placed on an export view and associated window the export value is 0 or space. If some other attribute is updated on that window and a DB update performed, it is possible that the 0 or space value can end up on the database. Any further tests of IF attribute IS NULL will be false. We have got round this by applying triggers on NULLable columns on the database to set values to NULL where the value is 0 or space. There is one possible drawback with this approach - what if 0 means something in business terms? We haven't got this situation yet so will cross that bridge...
RE: Defaulting
a field to null
#BY : mathur
I have recently design a workaround, which involves fixing IEF genrated code(CICS/DB2).
If you need to know, please reply. samritu@bigpond.com
#SUBJECT:
Random Identifier Generator
#BY : c carter
RE: Random
Identifier Generator
#BY : Glenn Smyth
Depending on quiet how many will be required in the same second as a quick and dirty you can loop reversing the timestamp (see below), adding one on a clash and trying again (or add one then reverse depending on which suits your use better).
PS Even in an environment that supports higher precision timestamps, e.g. DB2, using the timestamp is not a good idea (unless you reverse it first) as it leads to extremely inefficient index organization (huge level of page splits and very deep tree level).
RE: Random
Identifier Generator
#BY : ratnakar
RE: Random
Identifier Generator
#BY : mathur samritu
Can't you use ORACLE sequenceing for unquine number generation?
RE: Random
Identifier Generator
#BY : Dave Pember
We are using Oracle's sequence number. We create the sequence number, a function to return the next number and a trigger on the table to put the next number into the appropriate column -
REM If sequence,
function or trigger names exceed oracle limits
REM <Table Name> will have to be abbreviated.
REM Create sequence for <Table Name>
REM The maxvalue can be amended to match the length of
REM column in the table to hold the sequence number.
REM Set other options, NOCYCLE, NOCACHE etc.
CREATE SEQUENCE ORA_<Table Name>_SEQ_NO
MAXVALUE 999999999999999999
NOCACHE
NOCYCLE;
REM Create Function to return the next value of the sequence number
CREATE OR REPLACE FUNCTION RETURN_NEXT_<Table Name>_SEQ_NO
RETURN NUMBER
IS <Table Name>_seq_no NUMBER;
BEGIN
SELECT ORA_<Table Name>_SEQ_NO.nextval INTO <Table Name>_seq_no
FROM DUAL;
RETURN(<Table Name>_seq_no);
END;
REM Create Before Insert trigger on table
CREATE OR REPLACE TRIGGER NEXT_<Table Name>_SEQ_NO
BEFORE INSERT ON <Table Name>
FOR EACH ROW
BEGIN
:new.<column_name> := RETURN_NEXT_<Table Name>_SEQ_NO;
END;
#SUBJECT:
Cursor with hold in Oracle
#BY : ochmann
I migrate DB2 to Oracle(Unix). How could I implement the cursor with hold ?? (Read Each loop with commit in EAB)
RE: Cursor
with hold in Oracle
#BY : george
If I understand you correctly,,,,there is no need for the with hold predicate (in a declare cursor) in Oracle. Oracle has allowed the fetch across commit for some time. IE it is implicitly there.
Warning: Oracle retains the right for this not to work and may return you a snap shot to old message. Can't remember whether increasing the number of recordsbetween commits or decreasing them in your long running batch jobs help... thinkit is increasing the number,,,, any way just restart the job in your script if the aelog.tmp contains that message...#SUBJECT:
LIKE and performance
#BY : Janet Evans
Does anyone have any information (papers, experience) of using LIKE with regard to performance?
We have a performance issue arising from the use of 'LIKE' statements and wildcards in search fields and are looking for some background.
RE: LIKE
and performance
#BY : Kevin Bingham
I worked as a DB2 DBA for 3 years and am now involved in supporting a CoolGen/Oracle/Unix environment. My experience tells me the following:
Try at all costs to avoid a LIKE clause, I am not too sure about Oracle, but DB2 definitely will do a tablespace scan if the string for the LIKE begins with a In DB2 if you have an index on the column in questions, and you start your string with a character other than a wild card, then it will use the index to "zoom" in on those rows in the table, and then do the LIKE match. I think the same applies in Oracle. If you can force at least the first character to be a searchable character, it may help your performance.
RE: LIKE
and performance
#BY : murat gungor
-------------------------------
#SUBJECT:
COOL:Gen 4.1 NT and DB2/2 Universal
#BY : ken mcintosh
Has anyone successfully used DB2/2 Universal, rather than DB2/2 2.1.2, with COOL:Gen 4.1 for Windows NT, and if so what did you have to do?
RE: COOL:Gen
4.1 NT and DB2/2 Universal
#BY : Preben Nilsson
RE: Same
question, but for AIX!!
#BY : ackdd
RE: COOL:Gen
4.1 NT and DB2/2 Universal
#BY : Doug Brown
As noted in your other reply the security models in UDB and NT 4.0 are a pain but other than that we've had a lot of success using this architecture.
#SUBJECT:
Disabling Automatic Date/Time Check
#BY : Willem Visser
Hopefully somebody can help me - I am using Cool:Gen 4.1A & Oracle 7.3. I have a date and time field on a form in which the user only will have to enter data sometimes. The problem comes in that if the date and time is read as null from the database (1/1/1), then Cool:Gen displays nothing and throws out a "Resource Initialization Error" - because there is no valid time or date entered.
Anybody knows how to disable this automatic check, or have a way around it? Any help would be appreciated.
RE: Disabling
Automatic Date/Time Check
#BY : DAVID MUELLER
Have you checked that the prad import view for the date field has not been defined as mandatory?