{"id":236,"date":"2023-02-22T17:11:28","date_gmt":"2023-02-22T16:11:28","guid":{"rendered":"http:\/\/www.sepchat.com\/SC\/?p=236"},"modified":"2023-02-23T15:09:15","modified_gmt":"2023-02-23T14:09:15","slug":"ibm-i-creer-une-table-temporelle","status":"publish","type":"post","link":"http:\/\/www.sepchat.com\/SC\/index.php\/2023\/02\/22\/ibm-i-creer-une-table-temporelle\/","title":{"rendered":"IBM i – Cr\u00e9er une table temporelle"},"content":{"rendered":"\n

Depuis la V7R3 IBM a introduit la notion de table temporelle.Cette fonctionnalit\u00e9 permet de traiter de mani\u00e8re simple vos historiques des donn\u00e9es (alimentation, consultation).<\/p>\n\n\n\n

 La mise en oeuvre est relativement simple.<\/p>\n\n\n\n

Les tables temporelles fonctionnent par paire. Une table pour vos donn\u00e9es, une table pour l’historique.<\/p>\n\n\n\n

La premi\u00e8re table contient toutes les donn\u00e9es de base m\u00e9tier, (vous pouvez utiliser une table d\u00e9j\u00e0 existante). Il faudra lui ajouter 3 champs d’horodatage pour pouvoir dater les enregistrements.<\/p>\n\n\n\n

La seconde table, contient l’historique des donn\u00e9es et a la m\u00eame structiure que la premi\u00e8re table (champs d’horodatage compris).<\/p>\n\n\n\n

Par exemple, la table des d\u00e9partements peut \u00eatre d\u00e9finie comme suit : <\/p>\n\n\n\n

CREATE OR REPLACE TABLE<\/strong> DEPARTMENT
      (DEPTNO    CHAR<\/strong>(3)       NOT NULL<\/strong>,
       DEPTNAME  VARCHAR<\/strong>(36)   NOT NULL<\/strong>,
       MGRNO     CHAR<\/strong>(6),
       ADMRDEPT  CHAR<\/strong>(3)       NOT NULL<\/strong>,
       LOCATION  CHAR<\/strong>(16),
       START_TS  TIMESTAMP<\/strong>(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN<\/strong>,
       END_TS    TIMESTAMP<\/strong>(12) NOT NULL GENERATED ALWAYS AS ROW END<\/strong>,
       TS_ID     TIMESTAMP<\/strong>(12) GENERATED ALWAYS AS TRANSACTION START ID<\/strong>,
      PERIOD SYSTEM_TIME<\/strong> (START_TS, END_TS),
      PRIMARY KEY<\/strong> (DEPTNO))<\/p>\n\n\n\n

Ici les champs d\u00e9di\u00e9s \u00e0 la table temporelle sont START_TS, END_TS etTS_ID.<\/p>\n\n\n\n

 Puis, on cr\u00e9e la table historique :<\/p>\n\n\n\n

CREATE TABLE<\/strong> DEPARTMENT_HIST LIKE<\/strong> DEPARTMENT<\/p>\n\n\n\n

Enfin, on lie les 2 tables :<\/p>\n\n\n\n

ALTER TABLE<\/strong> DEPARTMENT ADD VERSIONING USE HISTORY TABLE<\/strong> DEPARTMENT_HIST<\/p>\n\n\n\n

Il est ensuite facile de consulter la table telle qu’elle \u00e9tait il y a 6 mois par exemple :<\/p>\n\n\n\n

SELECT * FROM<\/strong>\u00a0DEPARTMENT\u00a0FOR SYSTEM_TIME AS OF CURRENT TIMESTAMP<\/strong>\u00a0– 6\u00a0MONTHS<\/strong><\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n
\n
Cet article vous a \u00e9t\u00e9 utile ? N\u2019h\u00e9sitez pas \u00e0 le partager.<\/a><\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

Depuis la V7R3 IBM a introduit la notion de table temporelle.Cette fonctionnalit\u00e9 permet de traiter de mani\u00e8re simple vos historiques des donn\u00e9es (alimentation, consultation).  La mise en oeuvre est relativement simple. Les tables temporelles fonctionnent par paire. Une table pour vos donn\u00e9es, une table pour l’historique. La premi\u00e8re table contient toutes les donn\u00e9es de base […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[8],"tags":[],"_links":{"self":[{"href":"http:\/\/www.sepchat.com\/SC\/index.php\/wp-json\/wp\/v2\/posts\/236"}],"collection":[{"href":"http:\/\/www.sepchat.com\/SC\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.sepchat.com\/SC\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.sepchat.com\/SC\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.sepchat.com\/SC\/index.php\/wp-json\/wp\/v2\/comments?post=236"}],"version-history":[{"count":2,"href":"http:\/\/www.sepchat.com\/SC\/index.php\/wp-json\/wp\/v2\/posts\/236\/revisions"}],"predecessor-version":[{"id":350,"href":"http:\/\/www.sepchat.com\/SC\/index.php\/wp-json\/wp\/v2\/posts\/236\/revisions\/350"}],"wp:attachment":[{"href":"http:\/\/www.sepchat.com\/SC\/index.php\/wp-json\/wp\/v2\/media?parent=236"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.sepchat.com\/SC\/index.php\/wp-json\/wp\/v2\/categories?post=236"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.sepchat.com\/SC\/index.php\/wp-json\/wp\/v2\/tags?post=236"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}