If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Date format in query window?

Started by camboo, 02 Jun 2014 06:31:25 AM

Previous topic - Next topic

camboo

Hi all!

I have a problem about different date formats in DataManager (v.10.2) query window.

So, if I first select a some fact build node in my DataManager, go to data source properties of it and select query tab, I am then able to execute an sql clause of it. Then as I have fields with date format in the result set, I see these fields in a format like YYYY-MM-DD HH24-MI-SS.

So my question is that how am I able to change that date format DataManager is showing there? My target is to get them in the format like DD.MM.YYYY.

Thanks for Your answer in advance :)

camboo

#1
...some more details about my problem

I am using Oracle SQL* Net driver for connection to Oracle 11 database. Oracle client version is 11.

User guide of my version of DM (http://public.dhe.ibm.com/software/data/cognos/documentation/docs/en/10.2.1/ug_ds.pdf) says for Oracle (via SQL*Net) DBMS Driver in page 479:

"Date Format Conversion
In previous versions of IBM Cognos Data Manager, by default, the returned value
was set to YYYY-MM-DD HH24:MI:SS.
This default has been removed. If you want to set the returned value to
YYYY-MM-DD HH24:MI:SS, you must now enable it using the variable named
DM_NLS_DATE_FORMAT_COMPATIBILITY."

--> In my config file D:\Program Files\ibm\cognos\c10_64\datamanager\dm.ini there is no variable DM_NLS_DATE_FORMAT_COMPATIBILITY defined. However, I get dates in format YYYY-MM-DD HH24:MI:SS in sql query window. So, which steps should I take to get dates in format DD.MM.YYYY?


My root problem here is that we are getting this kind of ORA error in DM etl loads:

...
DM-DBM-0400 UDA driver reported the following on connection dw_database:
UDA-SQL-0107 A general exception has occurred during the operation "execute".
ORA-12592: TNS:bad packet
...


This problem occurs now and them, so not every time we execute the load! Date format seems to cause that error, sometimes DM or DB driver handles these date formats in a way that it causes these bad packet errors.

I would be very thankful to get some tips how to get rid of that occasional error!

Francois51

Hello, I'm having the same ORA-12592 bad packet error, it happens for the first time last month and again last week, during the automatic night load. It hapenned on 2 different jobs, and when I restarted the jon mannualy with absolutely no changes, it worked, both times.
It never happened since we started automatic loads in 2008.

For me it looks like it's related to an external cause (oracle client network substrate bug, network high loads...).

Still looking into it, do you have any update on this?

Francois51

Update to my post : It happened with COGNOS DataManager last month, which version was 8.4 running on Windows 2003 server and it happened again last week whereas I've had upgraded to version 10.2.1 on windows 2008 server.
Source Oracle database 10.2.0.5 and Oracle target database (DataWarehouse is 11.2.0.4)