Get last changed date of AS400 DDS file in Java

Question

Hello I'm working with Java connecting to an IBM AS400.

I want to read the timestamp of the last change of a DDS file, e.g. the timestamp of the last SQL operation (INSERT INTO MyLib/MyDDS VALUES (...)).

In the 5250 terminal, I can go to "work with objects" and open the metadata with choosing option 5 (=show) or simply DSPFD FILE(MyLib/MyDDS). There is the timestamp.

Another way is to run dspjrn jrn(MyLib/MyJournal) to inspect the journal and its timestamps.

How can I access the timestamps from Java, e.g. using JTOpen or SQL?

Thank you :)

For a database file, you need to look at member change date rather than object change date or object last-used date. Work with Objects does not accurately show date of the last data update. You would have to look at member details with DSPFD. (WRKOBJ **often** shows the right date, but only by coincidence when it does.) Answers so far might not be appropriate. — user2338816, Nov 06 '15 at 00:42
ok. I just tried, and yes it behaves like you said. DSPDF shows the right Date, WRKOBJ does not. WRKOBJ has the right Last-Used-At but only the day, not a timestamp — KeKru, Nov 06 '15 at 11:44
And keep in mind that LastUsed for the object doesn't imply that data from any member was ever read or written. Simply running OPNDBF is an example of enough to trigger LastUsed without any I/O. — user2338816, Nov 06 '15 at 13:06
Simplest CL might be **rtvmbrd mylib/myfile chgdate( &chgDate )** with any appropriate member name included if it's a multi-member file. I'll have to dig for any appropriate JTOpen equivalent. — user2338816, Nov 06 '15 at 13:14
RTVMBRD is not available on my system... but I'll try on my new testing AS400 in a few days. In JTOpen it could work with `CommandResult result = CommandConnection.getConnection("host", "user", "pass").execute("rtvmbrd mylib/myfile chgdate( &chgDate )");` (from the JTOpenlite library) — KeKru, Nov 06 '15 at 13:38
RTVMBRD is a native command and should always exist. I wouldn't expect it, but authority might restrict access. Although it's not JT400, maybe others know a class that has a related method. — user2338816, Nov 08 '15 at 00:53
See [com.ibm.as400.access.MemberDescription](http://javadoc.midrange.com/jtopen/index.html?com/ibm/as400/access/MemberDescription.html) field CHANGE_DATE_AND_TIME. — user2338816, Nov 09 '15 at 02:55
Thanks, it works with com.ibm.as400.access.MemberDescription and field CHANGE_DATE_AND_TIME :) — KeKru, Nov 12 '15 at 12:23

score 3 · Answer 1 · answered Nov 05 '15 at 17:37

3

SYSTABLESTAT seems to have the information you are looking for.

SELECT LAST_USED_TIMESTAMP
FROM QSYS2/SYSTABLESTAT
WHERE TABLE_SCHEMA = 'MyLib' AND TABLE_NAME = 'MyDDS'

answered Nov 05 '15 at 17:37

James Allman

40,573
11
57
70

Thanks, but for some reason I don't have a SYSTABLESTAT in QSYS or QSYS2 on my testing system. I'll try it on a new testing system in a few days – KeKru Nov 06 '15 at 12:27
systablestat is the table name works great. also lastchg is what you want last used is only a date. – danny117 Nov 09 '15 at 14:42
@KeKru What OS version are you on? I'm seeing SYSTABLESTAT back to at least 5.4, but I don't see any reference in the 5.3 Knowledge Center (though that doesn't mean it doesn't exist). The other thing is that you don't have authority to that catalog. By default *PUBLIC has authority to it, but your administrator may have removed that authority. – Kevin Adler Nov 19 '15 at 01:17
@Kevin Adler I'm working on the free public AS400 from http://pub1.de for testing purposes, until I get access to the customer's server. Yeah I think there are many authority restrictions on this public AS400 – KeKru Nov 20 '15 at 15:10

score 1 · Answer 2 · edited Nov 12 '15 at 13:14

1

You can use the ObjectDescription object to get the last change date.

AS400 as400 = new AS400();
ObjectDescription od = new ObjectDescription(as400, "YOURLIB", "FILENAME", "FILE");

Date lastChangeDate = (Date)od.getValue(ObjectDescription.CHANGE_DATE);

Edit: use MemberDescription and MemberDescription.CHANGE_DATE_AND_TIME

edited Nov 12 '15 at 13:14

KeKru

444
3
13

answered Nov 05 '15 at 20:39

David G

3,940
1
22
30

thanks, but it only returns an old date (I guess, it's the last change of the data definition), but not the timestamp of the last data update (Insert into...) – KeKru Nov 06 '15 at 12:21
Odd, before I posted the answer I ran a test by taking an old table, changing a record, and then ran that code ... the date it returned was the just a few moments before. – David G Nov 06 '15 at 16:45
From the linked class definition: _"Note: This class is not intended for use with database file members (type *MBR). To retrieve attributes of file members, use MemberDescription."_ In general, updating a member's data shouldn't update the _object_ CHANGE_DATE. (It doesn't on my systems.) – user2338816 Nov 09 '15 at 19:16
It is working with MemberDescription :) AS400 as = new AS400(...); MemberDescription od = new MemberDescription(as, "YOURLIB", "FILENAME", "FILENAME"); Date lastChangeDate = (Date)od.getValue(MemberDescription.CHANGE_DATE_AND_TIME); – KeKru Nov 12 '15 at 12:45

score 1 · Accepted Answer · answered Nov 12 '15 at 14:40

Java Specific

JT400 (and JTOpen) provides classes and methods that interface with system APIs such as QUSRMBRD and return values usable by Java functions.

For member data changes (and other attributes), see com.ibm.as400.access.MemberDescription field CHANGE_DATE_AND_TIME.

General

Native physical files (PFs) can allow multiple data members that can be separately updated. Members are essentially separate objects (of object type *MBR) from the files (of type *FILE) that contain them. The IFS presents database files as 'containers', i.e., as if they are directories. The members are presented much like streamfiles in a directory, and changes to the content of individual members is tracked by member since the containing file objects only have single 'change' dates.

RTVMBRD is a native command and should always exist. I wouldn't expect it, but authority might restrict access. This would be how CL would normally retrieve data change dates. Alternatively, the Retrieve Member Description (QUSRMBRD) API can be used by any language to retrieve member details.

Works for me :) AS400 as = new AS400(...); MemberDescription od = new MemberDescription(as, "YOURLIB", "FILENAME", "FILENAME"); Date lastChangeDate = (Date)od.getValue(MemberDescription.CHANGE_DATE_AND_TIME); — KeKru, Nov 12 '15 at 15:28

score 0 · Answer 4 · answered Nov 07 '15 at 15:28

0

Use this on your query...

SELECT LAST_USED_TIMESTAMP
FROM QSYS2/SYSTSTAT
WHERE (TABLE_SCHEMA, TABLE_NAME) = ('MyLib', 'MyDDS')

answered Nov 07 '15 at 15:28

Jairo R. Flores

724
4
11

Not working for me, SYSTSTAT or SYSSTAT is not available on my (very strange ^^) system – KeKru Nov 12 '15 at 12:37

score 0 · Answer 5 · edited Nov 08 '18 at 17:49

0

Using QSYS2/SYSTSTAT or QSYS2/SYSTABLESTAT will be slower in response as it is using join files. To achieve this with good performance use

SELECT DBXATS
   FROM QSYS/QADBXREF
WHERE (DBXLIB = 'MyLib' and DBXFIL = 'MyDDS')

edited Nov 08 '18 at 17:49

brass monkey

5,841
10
36
61

answered Nov 08 '18 at 12:16

Ahsan Habib

1

Get last changed date of AS400 DDS file in Java

5 Answers5