Querying xmeta (in Oracle)

[PaulS]

Hi All,

Anyone got a nice example of a time conversion from an xmeta repository residing in an oracle db please?

The one I'm looking at is the lock table,... LOCKED_AT is a number(38,7 ),.. what's that?

desc XMETA.XMETALOCKINFO
Name Null Type
REPOSITORY_ID NOT NULL VARCHAR2(64)
LOCKED_AT NOT NULL NUMBER(38,7)
LOCKED_BY_USER NOT NULL NVARCHAR2(256)
LOCKED_BY_SESSION NOT NULL NVARCHAR2(256)
NEEDS_UPGRADE NUMBER(1)

I think the below is from a DB2 repo..

--Query to list the jobs that are locked
SELECT
A.NAME_XMETA, B.LOCKED_BY_USER
(TIMESTAMP('01/01/1970', '00:00:00') + (B.LOCKED_AT / 1000) SECONDS) AS LOCKED_AT,
FROM
XMETA.XMETALOCKINFO B,
XMETA.DATASTAGEX_DSJOBDEFC2E76D84 A
WHERE
A.XMETA_REPOS_OBJECT_ID_XMETA = B.REPOSITORY_ID;

Ta
Paul

[eph]

Hi,

I believe that you can use the sql below :

Code: Select all

TO_CHAR (
                        (TO_TIMESTAMP_TZ ('1970-01-01 Europe/London',
                                          'yyyy-mm-dd tzr')
                         + NUMTODSINTERVAL (
                              B.LOCKED_AT / 1000,
                              'second'
                           ))
                        + 2 / 24,
                        'DD/MM/YYYY HH24:MI:SS'
                     )

Eric

[PaulS]

Perfect - works a treat! Thank you.

Paul