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
We are having
problems with Decimal Precision, whether the attribute is set with 'Implement
in C with Decimal Precision' or not. All amounts less than 1 are set to
zero on the database. However, we do not loosedecimals in amounts > 1.
(Sequent Unix and Oracle)We read the LinkFaq, but saw that patch RTX4101
causes other problems with minus values.
#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
It seems there
is no solution to your problem in cool:gen.I have similar problem, i accept
numeric input form screenand if input is zero, i need to put null to database
colum.I can't set export view to NULL. Any comment will be appreciated
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
I believe the
tech. design option will only do it only if the attribute is not specified
in the view (but its been a while since I tested this). In Cool:gen you
can do this but only by coding a separate create/update i.e.
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
I am looking
for a way to generate a unique identifier of type number. I am running an
Oracle/UNIX environment. I was going to use a date/time stamp, but found
out the Oracle does not go lower than seconds, which is not exact enough
for my needs. I was also going to use Cool:Gen's RandomNumber functions,
but found out that they cannot be used in UNIX.
RE: Random
Identifier Generator
#BY : Glenn Smyth
I have an EAB
that generates a random number in C++ that I will send you if you e-mail
me. However, even for random numbers you have to cater for non-unique keys
and then retry on clashes. I have e-mail addresses for two sites - DEETYA
in Australia and Orygen in Ireland - that both have an infrastructure component
for generating guaranteed unique system identifiers (though the components
have a lot more functionality that you probably require).
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
What we did
to get an Random Number is we multiplied the Second Timestamp times Microsecond
Timestamp value and checked for a duplicate value and set it to the view
of number(15). We felt that the chance of getting a duplicate value in this
way will be very remote.
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
Cool:gen generates
the SQL for LIKE predicates, naturally, as ...column LIKE :host-variable...And
I know from my experiences that, if LIKE is used with a host-variable(not
a scalar value) then DB2 precompiler does not(and can not) use the index
even if the host-variable value does not start with a wild-card(DB2 creates
an access strategy and stores it and does not re-analyze the statement at
run-time). Once when I faced with this problem, I had to use dynamic version
of the statement. In that case, you supply the value directly as ...column
LIKE 'value'..., and now DB2 can predict if it can use the index or not.
I think there was some postings about creating and using dynamic queries...
-------------------------------
#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
Not much really,
Simply install the 2 CD's (database system and development API), and your'e
running. We actually upgraded because of the non-existence of user interfaces
to the database. V.5 has a nice user interface, but still lacks a query
manager like in the "old days". Instead we are using the ODBC driver to
access DB2/NT from MS query. That seems to work very well.You will have
the same issues about local administrators on NT, sysadms in DB2 etc. as
you had in v. 2.1 of DB2/NT.
RE: Same
question, but for AIX!!
#BY : ackdd
Can anyone
please help with the same information for AIX? We have a production environment
running DB2 2.1.2 under AIX, which is being retired by IBM at the end of
1998. DB2 Universal database will then become the only version supported
by IBM (but not yet by Sterling). We are following this up with Sterling,
but would be grateful if anyone has some practical experience at this stage.
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?
|