Error creating Derived Table - XI 3.0 DB2

[Former Member]

I am modifying a universe in XI 3.0 and have encountered an error creating a simply derived table. The syntax of the derived table sql is correct, as it works in XI R2 designer.

Using a DB2 database, I receive the following error when I click "Check Syntax" button:

Quote:

Exception: DBD, [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0104 - Token ` was not valid. Valid tokens: AS IN OUT <IDENTIFIER>. State: 42000

The only thing I see strange is that the derived table name change the name typed DerivedTable to `DerivedTable`. Is there a way to keep Designer from adding this character? Any other ideas on what it may be?

Thanks for any input.

[Former Member]

The work around is to create a connection that is Generic->Generic ODBC Datasource ->ODBC Drivers and use the same ODBC that was not working before.

I asked BO Support to pass this on to their product team so that a fix will be developed, since the version of iSeries we use (5.4) is supported.

[Former Member]

Hi There,

We have tried this work around and still it does not work - Is there a fix out for this error?

[PPaolo]

Hello,

are there spaces in the name of the derived table? The system automatically adds quotes around a derived table which contains spaces.

Otherwise, there are 3 parameters in the db2iseries PRM file which you could play with to change the behaviour of quotes around table and column names:

IDENTIFIER_DELIMITER

BACK_QUOTE_SUPPORTED

DELIMIT_IDENTIFIERS

The Data Access guide which explains how to use those parameters can be found here:

http://help.sap.com/businessobject/product_guides/boexir31SP2/en/xi31_sp2_data_acs_en.pdf

Regards

PPaolo

[Former Member]

Hi Pierpaolo,

Thanks for this the solution worked

[PPaolo]

Hello Gary,

can you please share with the other participants which part of the proposal actually worked for you?

Thanks

PPaolo

[Former Member]

Came across this thread because I was having the same problem and wanted to respond with the specific fix for anyone else who has the same issue. You must add all three parameters to the db2iseries.prm file --- note, the parameters do not exist in the file, so you must add them in.

Specifically, you need to edit:

C:\Program Files\Business Objects\BusinessObjects Enterprise 12.0\win32_x86\dataAccess\connectionServer\db2\db2iseries.prm

You must add:

<Parameter Name="BACK_QUOTE_SUPPORTED">N</Parameter>

<Parameter Name="DELIMIT_IDENTIFIERS">NO</Parameter>

<Parameter Name="IDENTIFIER_DELIMITER">"</Parameter>

Restart the Universe design, import or open your universe and it should now work properly.

[Former Member]

Updating my prior post because making the changes caused other issues with quotes being added to table names. After working with tech support here is the final version of the changes needed to resolve all these issues:

Edit: C:\Program Files\Business Objects\BusinessObjects Enterprise 12.0\win32_x86\dataAccess\connectionServer\db2\db2iseries.prm

Add these two lines:

<Parameter Name="BACK_QUOTE_SUPPORTED">N</Parameter>

<Parameter Name="TABLE_DELIMITER"> & quot;</Parameter>

NOTE: There should not be a space between the & and quot in the TABLE_DELIMITER line, but the forum would not display the text properly unless I added the space

[frikkie_nagel]

Hi There,

This is how I got it to Work on a windows server 2008

1. Install IBM iSeries Access for Windows

2. Create an ODBC connection on the your BOE windows server with this iSeries Access ODBC driver

3. In the universe designer make sure your connection created is Client Access AS400

3.1. New Connection Wizard, Select IBM then DB2 UDB for iSeries v5 then IBM iSeries Access

3.2. For Alias Select the ODBC created in step 2 above.

4. Explore to <DRIVE>:\Program Files (x86)\Business Objects\BusinessObjects Enterprise 12.0\win32_x86\dataAccess\connectionServer\db2

5. Open the db2iseries.prm file

find the following line <Parameter Name="DELIMIT_LOWERCASE">YES</Parameter>

Add the following 2 lines after the above line

<Parameter Name="DELIMIT_IDENTIFIERS">YES</Parameter>

<Parameter Name="IDENTIFIER_DELIMITER">"</Parameter>

This give you " double quotes around your tables and columns

Hope this Help

Regards,

Frikkie

[Former Member]

Hi

I am designing my universe on top of db2 data using the iseries odbc conenction.

Ineed to use two tables F4211(sales order current) and F42119(sales order history)

Basically what I am trying to achieve is design a derived table on these two table using the UNION ALL.

I am gud till this part, but when I create an object on it and use it in webi report it doesn't show me any record it just keeps going.

any idea folks, onhow we can use these two tables effectively while designig universe.