JDBC/SQL access to D3

[Nibble]

Hi

I just want to know if anybody knows a way to access D3 data in a "relational" way, using JDBC/SQL. Of course I assume that the D3 files must be created with a strict "relational" format,.

I've been playing with the D3 MVSP API from Java but the integration protocol is d3-oriented.

Thanks in advance!

[David Knight]

I'm not familiar with JDBC but d3 has the ability to expose itself via ODBC. Personally my experience is with d3 on Windows; not *nix. There is a downloadable free ODBC client available via Rocket which connects to the d3 ODBC server which needs to be running also. This allows any ODBC compatible client application such as MS Access etc to use d3 data.

The only catch is you have to run a command to 'expose' the bits of d3 you want. The exposure setup handled multivalues and typing for you. Read up on commands like SQL-CREATE-TABLE etc. The good news is that only needs to be run once and the interface is good to go; even through restarts.

There are also 3rd party utilities available which can help, too. But I'm not familiar with those.

Only takes a few minutes to setup and test the connection, so I recommend you give it a try.

HTH

[David Hurst]

Hi

We have every D3 transaction made available to a SQL data store (in real time) so we can manage customers in Microsoft Dynamics CRM.

We also expose these transactions and other D3 transactions (files) to a SQL data warehouse (again in real time) for BI reporting. Management wanted to use industry standard tools and the usual thought was lets dump D3 and move to SQL. Exposing all our data to SQL has been a win win situation for the organisation. D3 remains core to the business for warehousing, accounting (although this will move to Navision), stock control etc.

To achieve this we use the brilliant MVS Toolkit to expose the data. We have a middleware layer that updates the SQL database in real time.


Also using the MVS Toolkit we have been able to develop very modern graphical interfaces with third party developers. The MVS Toolkit exposes D3 data as an object to PHP, .NET, C# etc. There is no need to learn D3 for a new developer and all your business rules remain inside D3. Again a win win win situation, D3 looks and feels modern and can use all industry standard tools.

One of the other benefits we have got from using the Toolkit, D3 basic code is managed with Visual Studio for version control again this is done via the Toolkit. (I know this has been a topic covered on here).

IF you want to know more about this please feel free to contact me off list.

The main D3 data base is on a quad processor IBM running AIX. The main MVS Toolkit server component resides on Windows 2008 R2 server.

Thanks

David


We achieve this using

--
You received this message because you are subscribed to
the "Pick and MultiValue Databases" group.
To post, email to: mvd...@googlegroups.com
To unsubscribe, email to: mvdbms+un...@googlegroups.com
For more options, visit http://groups.google.com/group/mvdbms

______________________________________________________________________
This email has been scanned by the Symantec Email Security.cloud service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________

-- 
This e-mail and any attachments hereto are:-
1. Strictly confidential and intended solely for the addressee.
2. If you are not the intended addressee, you must not disclose, forward, copy or take any action in reliance on this e-mail or attachments.
3. If you have received this e-mail in error, please notify the sender as soon as possible.
4. Not intended to create contractual relations or legal obligations binding on the sender and no action may be taken in reliance on this e-mail and any attachments hereto unless the contents are confirmed by a signed letter from the sender.
5. Addressees should check all attachments for viruses. The sender makes no representations as regards the absence of viruses in attachments to this e-mail

______________________________________________________________________
This email has been scanned by the Symantec Email Security.cloud service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________

[Nibble]

Thanks! I know that ODBC API but we're focused on a *nix oriented solution.

[Nibble]

Thanks David!

Our approach is somewhat different (but not too much), we don't want to move data from D3 to a SQL Database, we want to stay with the D3 database but try to tune it in a way that if some day we want to move from D3 to ¿Oracle? the effort could be acomplished without years of hard work and headaches.

With this in mind, we want to incrementally "clean" the D3 Database to make it more "SQL Oriented" (Add dictionaries, no multi-values, no stored-procs) and try to use it like a relational database.

We want to move our code to Java, so if we could directly work with D3 as a relational database with JDBC it would be awesome.

Speaking about that MVS Toolkit based middleware, please could you give me more information about the way this layer works?

Regards.

[David Knight]

You should probably speak to your system VAR or Rocket directly as well: http://www.rocketsoftware.com/products/rocket-mvs-toolkit

I expect there are many ways to do what you want; but frankly, I'm confused as to why you would do it?

If a toolkit allows you to do everything you need AND have all the mv benefits; why make it all 'relational'-like? If eventually you are going to move off d3, then you would do a data migration which by its very nature would [likely] require the data to be flattened; which the MVS tools will help you with anyway.

Seems like a *lot* of premature unnecessary work to me...

Just my 2c worth...

[David Hurst]

Hi

From what you are saying and I agree with David, you do not need to use this.

At the moment D3 Data is exposed as an object and we use java and jquery with a php front end all done through the toolkit, this flattens your data.

You can use AQL (Access) statements also to expose your data fromm within the toolkit, the toolkit sounds ideal for what you want I would seriously go and look at this, as this software is superb.

If you need any help on using the toolkit contact me off list and we can arrange something.

Thanks

David

[Anthony Youngman]

On 08/05/14 08:12, Nibble wrote:
> Thanks David!
>
> Our approach is somewhat different (but not too much), we don't want to
> move data from D3 to a SQL Database, we want to stay with the D3
> database but try to tune it in a way that if some day we want to move
> from D3 to ¿Oracle? the effort could be acomplished without years of
> hard work and headaches.
>
> With this in mind, we want to incrementally "clean" the D3 Database to
> make it more "SQL Oriented" (Add dictionaries, no multi-values, no
> stored-procs) and try to use it like a relational database.

In other words, you want to cripple it so the PHBs have an excuse to
move to Snoracle or some other crapware :-(

I'm sorry, but relational is MATHEMATICS, and as such is great for
helping you design your database, but it's crap as an engineering
solution. Any First Normal Form database is crippled (engineering-wise)
by its basic design, and this is a completely UNavoidable consequence of
FNF.

Each FILE should contain a Third Normal Form (near enough) object. That
is good Pick/MV design. You can then expose that to SQL no problem at all.

If you want to clean up your data there are plenty of tools to help. I
think you'll find a bunch of BASIC programs I wrote on PickWiki that go
through your dictionary and make sure the data is clean. It might
actually make more sense, though, for you to write a bunch of
integrity-check programs that go through your data every night or
weekend or whenever making sure the data is clean. If you've got a lot
of data, make sure you can specify ranges, so you can keep the latest
stuff clean while slowly going back and cleaning the earlier stuff. I
remember running a program someone else had written, that did just that
and normally it rarely reported errors. But I accidentally ran it "from
the year dot" and the amount of years-old errors it found was amazing!

Universe (and Unidata?) have HyperStar - Rocket might port that to D3,
or they might give you the basic code to some of the utilities, like
HS.SCRUB, that will also go through and clean up data.

Cheers,
Wol

[Dawn Wolthuis]

I have not used JDBC with D3 but I have with other MV DBMS tool sets. The good news is that you do not need to (and definitely should think long and hard before you would) normalize the data in D3. Instead, you use the tools to project the metadata as tables. The projection tools (for SQL/ODBC) will do the heavy lifting. I do not know if there are read/write SQL projection tools for D3. Having used them in other environments, I would advise looking at other ways to do Java with your database, but a SQL projection approach might do the trick.

I will caution you to drop entirely your thinking that you would be cleaning the database to drop multivalues or otherwise put you data back in time to the SQL-92 thinking. The web has helped move the industry although there is so much legacy SQL, that will take some time. D3 might or might not (more likely) be the future, but the underlying data model is much closer to where the industry is heading than is SQL/JDBC. --dawn

Sent from my iPad

[ERNIE VEGAS]

Good point Dawn. There is always a way to implement the equivalent of a multivalues in SQL, even if you have to implement parent/child tables.

Robert Norman

ROBERT NORMAN AND ASSOCIATES
http://universe-basic-programming.webstarts.com
http://users.keyway.net/~ice/index.html
http://www.affordablemultivalueprogramming.i8.com

Computer programming & software development in Universe BASIC for Rocket Universe, in PICK/BASIC for Rocket D3, in UNIBASIC for Rocket Unidata, in R/BASIC (REVELATION BASIC) for REVELATION TECHOLOGIES REVELATION, in JBASIC (JBC or JBASE BASIC) for TEMENOS JBASE, in DATA/BASIC for Northgate REALITY, in MV BASIC (Multivalue Basic) for Intersystems Cache Multivalue, and QMBASIC for Ladybridge OPENQM (QM).

[Tony Gravagno]

I don't remember the details. I haven't done this in over a decade. But I believe the ODBC interface is Type-2 Native JDBC compliant. You just can't access it via ODBC from *nix, requests must come through the Windows ODBC client interface. You can also use an interface from Fusionware - I believe this is the one to use:
http://www.fwic.net/Products/MultiValueProducts/FusionWaremvLynxJavaDataAdapter%28JDA%29.aspx

In agreement with others - I really think you guys are going in the wrong direction here. Don't dumb-down your system in anticipation of a migration to relational. At some point it will get so slow that someone's going to say "let's replace this old slow box" and the prophecy will be self-fulfilled. Use the environment as it was designed and you'll get excellent use from it like other companies. I have D3 supporting websites, web services, portals, communications, devices, and all kinds of applications - we never need relational access. ODBC is for people who want to bang everything with a hammer because everything looks like a nail to them. Eliminating your BASIC subroutines is silly. You're just replacing them with stored procs that will need to be re-written yet again if you move between RDBMS environments. Keep all of the rules in the DBMS and separate that from reporting tools.

I know this will meet with strong disagreement, but the only valid application for relational/ODBC access that I've seen is for specific kinds of reporting. The MV DBMS is great as for OLTP (transactions) but weak for OLAP (reporting). The solution here is often to export required data to off-line/near-line relational repositories specifically for reporting by relational tools. That includes common BI, dashboards, and all related tools. If you need access to live data, use triggers to write it to the repo, otherwise do batch updates every X minutes as required for specific classes of data. That satisfies the demands of management, without trashing the investment or versatility of the MV DBMS itself.

If you need help with this, feel free to contact me. Really, you don't need to go through all of this expensive and time consuming grief - but I sense yet another company is going to go down the path anyway...

Tony Gravagno
Nebula Research and Development
TG@ remove.pleaseNebula-RnD.com
http://Nebula-RnD.com/blog
Visit http://PickWiki.com! Contribute!
http://Twitter.com/TonyGravagno
https://bitbucket.org/foss4mv