|
We have an application developed in powerbuilder which has survived from the very early versions of SA. We are now on V11 and planning to move to V12. Historically we had been using a double as the datatype for our autoincrement. We ar enow finding that there are issues with the double when attempting to create immediate refresh materialized views. What are other good choices for autoincrement datatypes that will work with immediate refresh views? We will need to make sure these types are compatible with datawindow behavior (wherein the ID is refreshed in the ID column in the datawindow after an Insert) |
|
I'm somewhat surprised that a DOUBLE with DEFAULT AUTOINCREMENT does work...as to the docs:
However, such an approximate datatype might be error-prone w.r.t. rounding, as soon as the values are involved in arithmetics. (If you just read and write the values, that might be not problematic.) In addition to classic SMALLINT, INT and BIGINT types, I would expect NUMERIC(n, 0) (and DECIMAL(n, 0)) to work as well - they just require an integral value. Personally, I would always prefer the (BIG) INT types for performance reasons. I can't tell for Powerbuilder but all AUTOINCREMENT columns can be set automatically (and you can use SELECT @@IDENTITY to get the value afterwards), or can get pre-calculated values with SELECT get_identity(). - As of v12, SEQUENCES might be another alternative. |
|
IMO you should stick to BIGINT since that agrees with the data type for ISYSTABCOL.max_identity, the column that records the current largest value. IMO smaller integer types would be OK if space saving was important, but UNSIGNED BIGINT is asking for trouble, and fractional types are just not appropriate. CREATE TABLE SYS.ISYSTABCOL (
table_id /* PK FK */ UNSIGNED INT NOT NULL,
column_id /* PK */ UNSIGNED INT NOT NULL,
domain_id /* FK */ SMALLINT NOT NULL,
nulls CHAR ( 1 ) NOT NULL,
width UNSIGNED INT NOT NULL,
scale SMALLINT NOT NULL,
object_id /* FK */ UNSIGNED BIGINT NOT NULL,
max_identity BIGINT NOT NULL,
column_name CHAR ( 128 ) NOT NULL,
"default" LONG VARCHAR NULL,
user_type /* FK */ SMALLINT NULL,
column_type CHAR ( 1 ) NOT NULL,
"compressed" TINYINT NOT NULL,
collect_stats TINYINT NOT NULL,
inline_max SMALLINT NULL,
inline_long SMALLINT NULL,
lob_index TINYINT NULL,
CONSTRAINT PRIMARY KEY ( -- 16k
table_id,
column_id )
);
I noticed that my PowerDesigner 15 Physical Architect will not create a script to modify the existing data from a double to a big int. If I wish to preserve the values (which should be all integer values) , do I need to edit the inserts (commented with a warning) to do a cast of some form?
(11 Jan '12, 10:47)
Glenn Barber
Replies hidden
1
If you do something like
(11 Jan '12, 13:19)
Reimer Pods
|
|
AFAIK all numeric types can be used as Autoincrement. However, DOUBLE, being an approximate data type, is the least logical option. My choice for Autoinc columns is INTEGER, although NUMERIC(n) works nicely, too. |
|
Depends on what type of db you are using, read the manual and look for the largest capacity integer datatype, and go for it. normally the largest capacity integer datatype is BIGINT UNSIGNED. You will be able to represent integers from 0 up to 18446744073709551615. But then you should keep in mind, 18446744073709551615 is the largest integer accepted. Even if you assign length 50, it will still accept 20 digits only. I would not generally recommend BIGINT if you don't expect millions/billions of rows in your particular tables. If a smaller type does fit, I would go for it. (Though I would surely agree that's easier to use one AUTOINCREMENT type in a single database than several ones for different table sizes...)
(11 Jan '12, 06:27)
Volker Barth
I agree with Volker. In many cases INTEGER is sufficient, UNSIGNED INTEGER will go up to 4,294,967,295 - that means a a good many rows. Of course there are applications where that number isn't enough, that's where BIGINT comes into play.
(11 Jan '12, 07:35)
Reimer Pods
Yes guys, you all are right. In most of the cases INTEGER is enough to use. That is up to the developer himself. He must be knowing which is sufficient for his db, how many rows he might have etc. I suggested BIGINT because the user said they had been using a 'double', so I figured out they must have a db where the number of rows can be up to 15 digits.
(11 Jan '12, 07:49)
Bose
The reason for the double is lost somewhere in the dim early beginnings of powerbuilder and its support for auto increment datatypes . There is still the question whether powerbuilder V11 will support the bigint as a auto increment datatype. Note that the powerbuilder datawindow automatically updates the applied auto increment value into the data window after an insert- so we have to make sure whatever datatype we convert to is supported by that behavior. For our purposes unsigned integer would be sufficient and probably the most likely candidate.
(11 Jan '12, 11:00)
Glenn Barber
|
I am just curious, what is your problem with doubles and materialized views?
When attempting to create an Immediate Refresh material view SA11 gives a message something like "approximate datatypes are not supported". It does not flag it as a problem when the material view is not immediate.