Good morning,
Using Denodo express to test out several possibilities for generating base views for an IBM i we are looking at generating the VQL for parts of the base view and replacing those particular portions in the Denodo generating base view. This is because of the cryptic naming convention used in a major portion of the tables. And with over 9000 tables in a single DB2 schema, manually editing the base views would be a laboreous task.
So my issue, I have gotten down to the "CREATE OR REPLACE TABLE" statement so far and now have the following issue, when I replace this
CREATE OR REPLACE TABLE "bv_FRP003_freight_bill_events_copy" I18N us_est (
origin_terminal_id:int (notnull, sourcetypeid = '4', sourcetyperadix = '10', sourcetypedecimals = '0', sourcetypesize = '3'),
pro_number:int (notnull, sourcetypeid = '4', sourcetyperadix = '10', sourcetypedecimals = '0', sourcetypesize = '7'),
sequence_number:int (notnull, sourcetypeid = '4', sourcetyperadix = '10', sourcetypedecimals = '0', sourcetypesize = '3'),
terminal_id_for_activity:int (notnull, sourcetypeid = '4', sourcetyperadix = '10', sourcetypedecimals = '0', sourcetypesize = '3'),
activity_date_yymmdd:int (notnull, sourcetypeid = '4', sourcetyperadix = '10', sourcetypedecimals = '0', sourcetypesize = '6'),
activity_date_yyyymmdd:int (notnull, sourcetypeid = '4', sourcetyperadix = '10', sourcetypedecimals = '0', sourcetypesize = '8'),
activity_time:int (notnull, sourcetypeid = '4', sourcetyperadix = '10', sourcetypedecimals = '0', sourcetypesize = '4'),
activity_code:text (notnull, sourcetypeid = '1', sourcetypesize = '3'),
manifest_origin_terminal:int (notnull, sourcetypeid = '4', sourcetyperadix = '10', sourcetypedecimals = '0', sourcetypesize = '3'),
manifest_number:int (notnull, sourcetypeid = '4', sourcetyperadix = '10', sourcetypedecimals = '0', sourcetypesize = '5'),
trip_number:int (notnull, sourcetypeid = '4', sourcetyperadix = '10', sourcetypedecimals = '0', sourcetypesize = '7'),
comment:text (notnull, sourcetypeid = '1', sourcetypesize = '40'),
user_name:text (notnull, sourcetypeid = '1', sourcetypesize = '10', description = 'changed by user name')
)
FOLDER = '/1 - connectivity/2 - base views'
CACHE OFF
TIMETOLIVEINCACHE DEFAULT
ADD SEARCHMETHOD "bv_FRP003"(
I18N us_est
CONSTRAINTS (
ADD origin_terminal_id (any) OPT ANY
ADD pro_number (any) OPT ANY
ADD sequence_number (any) OPT ANY
ADD terminal_id_for_activity (any) OPT ANY
ADD activity_date_yymmdd (any) OPT ANY
ADD activity_date_yyyymmdd (any) OPT ANY
ADD activity_time (any) OPT ANY
ADD activity_code (any) OPT ANY
ADD manifest_origin_terminal (any) OPT ANY
ADD manifest_number (any) OPT ANY
ADD trip_number (any) OPT ANY
ADD comment (any) OPT ANY
ADD user_name (any) OPT ANY
)
OUTPUTLIST (activity_code, activity_date_yymmdd, activity_date_yyyymmdd, activity_time, comment, manifest_number, manifest_origin_terminal, origin_terminal_id, pro_number, sequence_number, terminal_id_for_activity, trip_number, user_name
)
WRAPPER (jdbc "bv_FRP003_0")
);
with this
CREATE OR REPLACE TABLE "bv_FRP003_freight_bill_events_copy" I18N us_est (
origin_terminal_id:int (notnull, sourcetypeid = '4', sourcetyperadix = '10', sourcetypedecimals = '0', sourcetypesize = '3'),
pro_number:int (notnull, sourcetypeid = '4', sourcetyperadix = '10', sourcetypedecimals = '0', sourcetypesize = '7'),
sequence_number:int (notnull, sourcetypeid = '4', sourcetyperadix = '10', sourcetypedecimals = '0', sourcetypesize = '3'),
terminal_id_for_activity:int (notnull, sourcetypeid = '4', sourcetyperadix = '10', sourcetypedecimals = '0', sourcetypesize = '3'),
activity_date_yymmdd:int (notnull, sourcetypeid = '4', sourcetyperadix = '10', sourcetypedecimals = '0', sourcetypesize = '6'),
activity_date_yyyymmdd:int (notnull, sourcetypeid = '4', sourcetyperadix = '10', sourcetypedecimals = '0', sourcetypesize = '8'),
activity_time:int (notnull, sourcetypeid = '4', sourcetyperadix = '10', sourcetypedecimals = '0', sourcetypesize = '4'),
activity_code:text (notnull, sourcetypeid = '1', sourcetypesize = '3'),
manifest_origin_terminal:int (notnull, sourcetypeid = '4', sourcetyperadix = '10', sourcetypedecimals = '0', sourcetypesize = '3'),
manifest_number:int (notnull, sourcetypeid = '4', sourcetyperadix = '10', sourcetypedecimals = '0', sourcetypesize = '5'),
trip_number:int (notnull, sourcetypeid = '4', sourcetyperadix = '10', sourcetypedecimals = '0', sourcetypesize = '7'),
comment:text (notnull, sourcetypeid = '1', sourcetypesize = '40'),
user_name:text (notnull, sourcetypeid = '1', sourcetypesize = '10', description = 'changed by user name')
)
FOLDER = '/1 - connectivity/2 - base views'
CACHE OFF
TIMETOLIVEINCACHE DEFAULT
ADD SEARCHMETHOD "bv_FRP003"(
I18N us_est
CONSTRAINTS (
ADD origin_terminal_id (any) OPT ANY
ADD pro_number (any) OPT ANY
ADD sequence_number (any) OPT ANY
ADD terminal_id_for_activity (any) OPT ANY
ADD activity_date_yymmdd (any) OPT ANY
ADD activity_date_yyyymmdd (any) OPT ANY
ADD activity_time (any) OPT ANY
ADD activity_code (any) OPT ANY
ADD manifest_origin_terminal (any) OPT ANY
ADD manifest_number (any) OPT ANY
ADD trip_number (any) OPT ANY
ADD comment (any) OPT ANY
ADD user_name (any) OPT ANY
)
OUTPUTLIST (activity_code, activity_date_yymmdd, activity_date_yyyymmdd, activity_time, comment, manifest_number, manifest_origin_terminal, origin_terminal_id, pro_number, sequence_number, terminal_id_for_activity, trip_number, user_name
)
WRAPPER (jdbc "bv_FRP003_0")
);
I am getting the error "Error calculating the capacity".
And a search of all the cummunity has revealed nothing as this error refers to base views.
Can anyone shed some light on what is actually wrong?
I thought it was an incorrect sourcetypeid, but I corrected that (It was a '4' and should have been a '1' on the text columns), after that corrections, I still get the same error.
