cancel
Showing results for 
Search instead for 
Did you mean: 

Access Tables in an Informix Database that have their table names in lower

Former Member
0 Kudos

Any help greatly appreciated!

Thanks

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Thanks.

Yes we are having trouble connecting to the view.

Have you been able to do the connection?

WE are using dbconnect.

0 Kudos

dear Frank,

check if helps oss note 520496 mentioned in doc

'Transferring Data with DB Connect'

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/2f0fea94-0501-0010-829c-d6b...

we are using db connect but never with informix.

hope this helps.

520496-BW 3.0 external DB Connect after INFORMIX

Symptom

You want to connect an external Informix database to a BW Release greater than 3.0A in the form of a source system via an external Database Connect. This note describes the following:

prerequisites to be met

problems that can occur

which of these problems can be solved.

Other terms

Rsdbc, DATES, TIMES, DBCON, DBCONNECT, source system, database, InfoSource, DataSource

Reason and Prerequisites

1. General

2. Supported BW / Basis versions

3. Possible main problem areas

a) Conventions and notation

b) Use of data types

c) Code page and sort sequence of the source system

1. General

-


Implementing the above function enables you to load data into a BW system from any Informix database. Unlike an Informix database delivered by SAP AG, you can configure this database as you like. This can cause constellations where an external DB access cannot be used directly. For most of these scenarios, a satisfactory customer-specific solution can be developed. However, the consulting expenses associated with this solution are not part of the normal support and are settled separately. A certain level of expertise and experience in using the source database (Informix in this case) is required to set up this connection, especially in the areas

DB-specific tools

DB-specific SQLSyntax

DB-specific function

Knowledge of the source application is also required for transferring semantically-relevant data to the BW system.

Solution

1. Supported BW/Basis versions

-


BW 3.0B Basis 6. 20 Support Package 21

1. Possible main problem areas

-


a) Conventions and notation

The DB connect interface in BW always expects capitalized table and field names. Since table and column names are usually written in lower case in Informix, access to an external table must be enabled via a view on the (external) Informix database. Field and table names must be entered in upper case in the view (enclose in quotation marks; environment variable DELIMIDENT must be set).

Another advantage of the view is that access rights to the external table can be adjusted accordingly for the new requests. The view should be created as follows:

create view "<VIEWNAME>" ("<FIELD1>", "<FIELD2>"..)

as select

<field1>, <field2>....

from <tabname>;

a) Use of data types

In the case of SAP installations, the DB data type DATE and DATETIME are not used. Date and time are stored by the DDIC as character-like data types on the database. These date types therefore cannot be copied to the BW consistently without additional activities.

Solution:

You must create a view in the source system view which transforms the date types. DATE must be converted to an CHAR8 field (date) and DATETIME to an CHAR8 field (date) and a CHAR10 field (time):

create view "<VIEWNAME>" ("<FIELD1>")

as select

substring ( to_char(<date_field>,'%Y%m%d) from 1 for 8 )

from <tabname>;

create view "<VIEWNAME>" ("<FIELD1>", "<FIELD2>")

as select

substring ( to_char(<datetime_field>,'%r) from 1 for 2 ) ||

substring ( to_char(<datetime_field>,'%r) from 4 for 2 ) ||

substring ( to_char(<datetime_field>,'%r) from 7 for 2 ) ||

'0000'

from <tabname>; #

Informix and ABAP support a value range of 1x10E-307 to 1x10E+308 for the 'float' data type. Limits may apply in the case of other databases. Before transferring 'float' data types to BW, you should therefore check the permissible value range on the target database.

a) Code page and sort sequence of the source system

-


R/3 kernel based systems like BW are configured so that the database used is created with code page cp850 and the sort sequence 'bin'. The configuration of the source system may deviate from this.

If the sort sequence is different, operations for pattern search ( like ) and area search ( between, >, < ) for character-like fields may return different results. No solution is currently available for this problem.

The use of multibyte code pages in the source system for storing character sets with more than 256 characters ( Kanji, Hiragana, Korean, Chinese etc.) can cause inaccuracies in the data. No solution is currently available for this problem.

Former Member
0 Kudos

Thanks Have passed onto our Technical people.

Most appreciated.

Will keep updated here.

0 Kudos

dear Frank,

you are welcome

thanks in advance for keep us updated,

hope you will share us the solution and the error,

whenever our friends encounter the same, we may refer to your solution which may very helpful to us.

Former Member
0 Kudos

Thanks,

This is the part that helped our technical resolve our issue.

Thanks a million.

This part:

........

1. Possible main problem areas

-


a) Conventions and notation

The DB connect interface in BW always expects capitalized table and field names. Since table and column names are usually written in lower case in Informix, access to an external table must be enabled via a view on the (external) Informix database. Field and table names must be entered in upper case in the view (enclose in quotation marks; environment variable DELIMIDENT must be set).

Answers (1)

Answers (1)

0 Kudos

dear Frank,

can specify more detail access from bw ? dbconnect ?

you can create view in informix with view name in upper case, this view select from the table(s).

hope this helps.