So does Documentum support unknown columns in DB table or not?

ilya basin

Documentum does not use the list of columns when it formats the INSERT SQL for a created object. Instead, it uses the alternate syntax with the values listed in the order in which the table columns are defined. For instance, if the table is defined like this:

CREATE TABLE mytype_s ( r_object_id, i_partition, myattr)

then the insert will look like this:

INSERT INTO mytype_s VALUES ('0b02254c814a181f',   0,'****')

After some experiments we found that if the DB table has columns that are not attributes (e.g. result of manual DDL manipulation), then Documentum adds the default value for this column (usually NULL).

CREATE TABLE mytype_s ( r_object_id, i_partition, old_column, myattr)<br>

_

INSERT INTO mytype_s VALUES ('0b02254c814a181f',   0, NULL, '****')

_

It's not clear at what moment Documentum reads the table schema from DB. Unlikely at server start only because it usually works fine after ALTER TABLE + ALTER TYPE.

But now we faced an issue that some object creations fail with Postgres error "INSERT has more expressions than target columns". After the last attribute change we restarted the server many times and the worst is that the error is not reproducible: the next object creation succeeds.

In our DB table we have one unknown column, but the INSERT SQL has two NULLs, one of them is positioned at wrong place!

Hicham Bahi

As far as I know, Documentum Server simply passes the INSERT statement to the underlying database and passes the result back.

To avoid such issues, simply specify the columns you want to set, e.g. INSERT INTO my_type_s (col1, col2, col3) VALUES ('val1', 'val2', 'val3')

Tejraj

If you are creating an object, you should be using CREATE OBJECT DQL and not INSERT query.

ilya basin

bacham3, Tejraj

The problem is with INSERT statements that are generated by Documentum when we run a CREATE OBJECT DQL or API call

Tejraj

Ideally it should not happen, are you getting error while executing CREATE OBJECT DQL or attributes are not populated correctly?
I would suggest to publish the data dictionary and test again if OBJECT TYPE is updated.
Can you please post the CREATE OBJECT DQL and object dump?

Hicham Bahi

You are right @Tejraj ! I misunderstood the post: I though @Basin was working with registered tables.

Tejraj

@bacham3 , in the original post query is mentioned like:
CREATE TABLE mytype_s ( r_object_id, i_partition, myattr)

this gives impression of registered table. So it is actually confusing if they are working with registered table or custom type.

ilya basin

@Tejraj

I can't dump the object because it's not created. However, now I see that the error only happens on the second Content Server Node:

Success:

$idql efxpbase -Uefxpm -Pignored

        OpenText Documentum idql - Interactive document query interface
        Copyright (c) 2017. OpenText Corporation
        All rights reserved.
        Client Library Release 7.3.0190.0080


Connecting to Server using docbase efxpbase
[DM_SESSION_I_SESSION_START]info:  "Session 0102254c80675542 started for user efxpm."


Connected to OpenText Documentum Server running Release 7.3.0210.0150  Linux64.Postgres
1> create efx_component_digital object link '/Temp', set object_name = 'testpostgres', set dcmi_title = 'testpostgres', set dcmi_identifier_isbn = 'Not assigned'
2> go
object_created
----------------
0b02254c814ed594
(1 row affected)
1> delete efx_component objects where object_name = 'testpostgres'
go
2> objects_deleted
---------------
              1
(1 row affected)

Failure:

$idql efxpbase.efxpbase2 -Uefxpm -Pignored


        OpenText Documentum idql - Interactive document query interface
        Copyright (c) 2017. OpenText Corporation
        All rights reserved.
        Client Library Release 7.3.0190.0080


Connecting to Server using docbase efxpbase.efxpbase2
[DM_SESSION_I_SESSION_START]info:  "Session 0102254c80675178 started for user efxpm."


Connected to OpenText Documentum Server running Release 7.3.0210.0150  Linux64.Postgres
1> create efx_component_digital object link '/Temp', set object_name = 'testpostgres', set dcmi_title = 'testpostgres', set dcmi_identifier_isbn = 'Not assigned'
2> go
[DM_OBJ_MGR_E_SAVE_FAIL]error:  "save failed for object with handle 0b02254c814ed440 of type efx_component_digital: table on which save failed was efx_component_s; error from database system was  STATE=42601, CODE=7, MSG=ERROR: INSERT has more expressions than target columns;
Error while executing the query
"

1> create efx_component_digital object link '/Temp', set object_name = 'testpostgres', set dcmi_title = 'testpostgres', set dcmi_identifier_isbn = 'Not assigned'
go
2> [DM_OBJ_MGR_E_SAVE_FAIL]error:  "save failed for object with handle 0b02254c814ed441 of type efx_component_digital: table on which save failed was efx_component_s; error from database system was  STATE=42601, CODE=7, MSG=ERROR: INSERT has more expressions than target columns;
Error while executing the query
"

Tejraj

Can you clear the type cache on content server (node giving error), restart it and test again?

ilya basin

@Tejraj Do you mean

API>flush,c,ddcache,object_type i.e. Where object Type is your custom type

?

Tejraj

Yes.

ilya basin

Thanks @Tejraj . That helped.