Intégration JSON à DB2.


DB2 Version 7.2 + TR2


Les niveaux de correctif pour Database level 5 (7.2) et 34 (7.1) de IBM i apportent à SQL l'intégration du langage JSON.

JSON (JavaScript Object Notation) est un format de données textuelles dérivé de la notation des objets du langage JavaScript (Wikipedia)

 

ce qui s'écrit comme cela en XML   s'écrit comme cela en JSON
<producteurs>
  <producteur>
    <numero>45</numero>
   <commune>Reims</commune>
    <appellation>13</appellation>
  </producteur>
</producteurs>		 ->

{
   "producteurs":  {
       "producteur": {
            "numero":45,
            "commune":"Reims",
            "appellation":13 
       }
    }
}


D'ailleurs vous trouverez ici deux fonctions bien pratiques XML2JSON et JSON2XML écrites en Java.

Un élément peut contenir un objet, une valeur ou un tableau de valeurs, marqué alors par [ et ]

<PO>
 <id>103</id>
 <orderDate>2014-06-20</orderDate>
 <customer>
   <cid>888</cid>
 </customer>
 <items>
  <item>
   <partNum>872-AA</partNum>
   <shipDate>2014-06-21</shipDate>
   <productName>Lawnmower</productName>
   <USPrice>749.99</USPrice>
   <quantity>1</quantity>
 </item>
 <item>
  <partNum>837-CM</partNum>
  <productName>Digital Camera</productName>
  <USPrice>199.99</USPrice>
  <quantity>2</quantity>
  <comment>2014-06-22</comment>
  </item>
 </items>
</PO>

devient (remarquez le tableau de "item")

{
 "PO":{
 "id": 103, 
 "orderDate": "2014-06-20", 
 "customer": {"@cid": 888}, 
 "items": { 
   "item": [
             { "partNum": "872-AA", 
               "productName": "Lawnmower", 
               "quantity": 1, 
               "USPrice": 749.99, 
               "shipDate": "2014-06-21" 
             }, 
             { "partNum": "837-CM", 
               "productName": "Digital Camera", 
               "quantity": 2, 
               "USPrice": 199.99, 
               "comment": "2014-06-22" 
             } 
          ] 
        } 
    }
}

résumé des caractères spéciaux


Il y a trois manières d'utiliser JSON sur IBM i :

Pour un accès RPG, voyez le projet YAJL

 

Pour l'accès en ligne de commandes, sous QSH, lancez /QIBM/ProdData/OS/SQLLIB/bin/db2nosql.

la première fois utilisez impérativement l'option -setup enable


Comme souvent, ce produit a des difficultés avec les codes pages "Latins"....

cela créé une table SYSJSON_INDEX et (s'il le faut) ré-enregistre les fonctions JSON/SQL dans SYSTOOLS





Pour accéder en mode commande, vous pouvez lancer QSH ou bien copier les fichiers suivants depuis /QIBM/ProdData/OS/SQLLIB/bin
sur votre poste :

Pour lancer

sous QSH

depuis un PC

 

Attention tous les ordres sont sensibles à la casse.

db.sqlUpdate

passe un ordre SQL (par exemple create schema)

use schema

défini le schéma (bibliothèque) en cours


Cette dernière n'a pas forcement été créée par db2nosql

db.createCollection

créé une collection JSON (soit une table dans le schéma en cours)



cette table a la structure suivante




db.collection.insert

insert des données JSON dans une collection

C'est un BLOB (binaire) au format BSON

la fonction BSON2JSON est là pour afficher au format JSON (CLOB)


en 5250 il faudra ajouter CAST(systools.bson2json(data) as char(128) ) .....
sinon, vous verrez



Comme avec XML, la conformité du format JSON est vérifiée



de nombreuses commandes sont disponibles ensuite




Liste des commandes : 

 -- Commandes générales
      Entrez 'help <méthode>' pour des informations détaillées.
 debug                 disable              enable 
 help                  use                  show 

    Syntaxe : "<method>"                                                                                                         
           
 -- Commandes de base de données
      Entrez 'help db <méthode>' pour des informations détaillées.    
    createCollection      dropAllCollections   dropDatabase 
    getCollectionNames     help                     
    sqlQuery              sqlUpdate            stats 
      
    Syntaxe : "db.<method> "     
                                                                                                                                 
 -- Commandes des collections
      Entrez 'help collection <méthode>' pour des informations détaillées.
    aggregate             count           
    distinct              drop                                                                                                   
    ensureIndex           exportFile           find 
    findAndModify         findOne         
    help                  group                importFile 
    insert                markType             remove 
    rename                sampleSchema         save 
    stats                 update          
    
      Syntaxe : db."collection".<méthode>
                                                                                                                                   
-- Commandes des curseurs
      Entrez 'help cursor <méthode>' pour des informations détaillées. 
    batchSize             help                 lazyFetch 
    limit                 size                 skip
    sort                                                                                                                         
                                                                                                                                   
      Syntaxe : "db.collection.find().<method> "


Exemples de commande : 
         db.friends.insert({name:"Joe", age:5})
            [Insérer dans la collection '"friends"'.] 
     
         db.friends.remove()
            [Supprimer toutes les lignes de la collection '"friends"'.]
 
         db.friends.find({name:"Joe"})
            [Trouver des amis dont le nom est '"Joe"'.] 

         db.friends.find({age:{$gt:5}})
            [Trouver des amis dont l'age est supérieur à  "5".]
 
         db.friends.find().sort({name:1})
            [Trier par nom, par ordre croissant.]
 
         db.friends.find().sort({name:-1}).limit(5)
            [Trier par nom, par ordre décroissant, renvoyer les "5" premières lignes] 

         db.friends.find().sort({name:-1}).limit(5).skip(10)
            [Trier par nom, par ordre décroissant, renvoyer les "5" premières lignes
                                                   ignorer les "10" premières lignes.]
 
         db.friends.importFile("C:\\myfriends.js")
            [Importation de masse dans la collection] 

         quit
            [Quitter l'interpréteur de commandes.]

Pour plus d'informations, voir ce document (destiné à l'origine à DB2 sur autres plate-forme)

https://www.ibm.com/developerworks/data/library/techarticle/dm-1306nosqlforjson2/

Pour l'accès en java, voyez cette page https://www.ibm.com/developerworks/data/library/techarticle/dm-1307nosqlforjson3/

Pour se connecter

  String url = "jdbc:as400://MONIBMi";
  Connection con = DriverManager.getConnection(url,  userid,  password);
  DB db = NoSQLClient.getDB(con, schema)


L'objet DB permet de passer des ordres SQL

  db.SQLUpdate("CREATE SCHEMA JSONBIB");


L'objet DBCollection donnes accès à une collection au sens JSON

DBCollection jcol = db.getCollection("JSONCOL");
col.insert("{'nom':'Chateau Margaux','appellation':'Bordeaux', 'commune' : 'MARGAUX'}" )

L'objet DBBasicObject offre un niveau d'abstraction avec un flux JSON

BasicDBObject json = new BasicDBObject();
json.append("nom","Coteaux de cherry");
json.append("appellation","Condrieu");
json.append("commune","Chavanay");

 col.insert (json);


Pour l'accès en SQL, il faudra utiliser les fonctions SQL fournies

Vous pouvez aussi utiliser les fonctions suivantes :






Exemple

CREATE TABLE POSAMPLE.JSONPO(
  ID INTEGER NOT NULL as identity,
  DATA BLOB(16M)
  DATCRT TIMESTAMP FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP
) ;		 


INSERT INTO  POSAMPLE.JSONPO (data) VALUES (
 systools.JSON2BSON('{
 "PO":{
   "@id": 103, 
   "@orderDate": "2014-06-20", 
   "customer": {"@cid": 888}, 
   "items": { 
     "item": [
       { "@partNum": "872-AA", 
         "productName": "Lawnmower", 
         "quantity": 1, 
         "USPrice": 749.99, 
         "shipDate": "2014-06-21" 
       }, 
       { "@partNum": "837-CM", 
         "productName": "Digital Camera", 
         "quantity": 2, 
         "USPrice": 199.99, 
         "comment": "2014-06-22" 
      } 
     ] 
    } 
   }
 }'
));

 

select json_val(data , 'PO.customer.@cid' , 'f') from posample.JSONPO;

-> 888,00
    


select json_len(data , 'PO.items.item') from posample.JSONPO;

-> 2






les Technology Refresh 1 (7.3) et 5 (7.2) intégrent une version officelle de JSON_TABLE PLUS COMPLETE



Voyez notre comparatif XMLTABLE vs JSON_TABLE


Enfin la TR3 apporte de nouvelles fonctions