Data Extraction from UNIX Database

pablo21

My company is moving from a Dealer Management System based on a Unix Server and I need to extract the data from the database to add to our new Linux based System. Does anyone have any experience with this type of work? The DMS system is IBCOS Gold with a windows interface.
All help greatly appreciated. NB I know nothing about Unix I'm afraid.

Tom Dunne

The IBCOS Gold just sounds like a piece of software, rather than a database. Is there any technical information on the software that might give an indication as to what RDBMS (relational database management system) is behind it? It could use a standard type of database (e.g. Oracle, DB2) , or it could use it's own custom database (unlikely, but you never know).

It's usually User<-->Software<-->Database, in your case the software is IBCOS. If you can find out the database, it would help greatly.

Also, whoever sold you the software probably has products that will do this for you.

pablo21

It was purchased directly from Ibcos www.ibcos.com. Its Version 6 windows gold, but I cant find any hint as to what type of database it is in the software itself. Its unix with a windows shell.

Tom Dunne

pablo21 wrote: »

It was purchased directly from Ibcos www.ibcos.com. Its Version 6 windows gold

I am confused. Windows gold suggests it is a Windows app, yet you say:

pablo21 wrote: »

Its unix with a windows shell.

What does that last bit mean? Do you interact with it via Microsoft Windows or a Windowed environment, such as X-Windows on Unix?

pablo21

Windows Gold is just a name, the server is unix and the interface is a windows shell.i.e. I access it through my windows PC.

Tom Dunne

pablo21 wrote: »

Windows Gold is just a name, the server is unix and the interface is a windows shell.

Still not getting what you mean by a windows shell.

pablo21

IP address of unix server is 192.168.1.2. Shortcut on my windows PC runs a .bat file on the unix server and it opens a "Windows" like screen on which I interact with the system. Shortcut looks like this 192.168.1.2/goldw/gold.bat

Tom Dunne

pablo21 wrote: »

IP address of unix server is 192.168.1.2. Shortcut on my windows PC runs a .bat file on the unix server and it opens a "Windows" like screen on which I interact with the system. Shortcut looks like this 192.168.1.2/goldw/gold.bat

Ok, gotcha. You probably have something like Exceed running on your Windows PC to display the interface from the Unix machine. Completely irrelevant to your problem, but it makes sense to me now.

Given how little information there is on the product, here's what I would suggest:

Log in using some sort of admin/superuser account and see if there is an export option. Look for and option to "export to delimited file" or "text file". In theory you should be able to import that into the new database, but it's back down to what type of database the new system uses and if the schema can support it.

The other alternative is contact the people who make the software and see if they have data migration tools. I would be surprised if they didn't. I would also be surprised if they didn't charge you an arm and a leg for them.

Without specific information, it's pretty difficult to give more detailed suggestions.

pablo21

I've scoured it and there's no sign of any export options, I've even had some of the senior guys from my new system (Kerrdige/ADP) look it over and they didnt have any joy either. For various reasons I'd rather not let my current supplier know I'm moving away from them until the absolute last minute but if I have to ask them to convert it I will. As you rightly pointed it they will probably dust off the saddle and put me on all fours when they hear I'm off to another company!

pablo21

Does Synergy/DE sound familiar to you?

http://www.synergex.com/media/article.aspx?id=2521

This describes our system exactly!

Tom Dunne

pablo21 wrote: »

Does Synergy/DE sound familiar to you?

http://www.synergex.com/media/article.aspx?id=2521

This describes our system exactly!

It's all fluff and no techie information. So no good.

Can you even log into the Unix machine? If so, can you list the processes running (use the command ps -ef ). This might give a clue as to what is going on.

pablo21

UID PID PPID C STIME TTY TIME CMD
root 0 0 0 09:17:26 ? 00:00:02 sched
root 1 0 0 09:17:26 ? 00:00:00 /etc/init
root 2 0 0 09:17:26 ? 00:00:00 vhand
root 3 0 0 09:17:26 ? 00:00:01 bdflush
root 4 0 0 09:17:26 ? 00:00:00 kmdaemon
root 5 1 0 09:17:26 ? 00:00:01 htepi_daemon /
root 6 0 0 09:17:26 ? 00:00:00 strd
root 7 1 0 09:17:26 ? 00:00:00 udi_alloc
root 8 1 0 09:17:26 ? 00:00:00 udi_region
root 182 1 0 09:20:28 ? 00:00:00 /tcb/files/no_luid/sdd
root 51 1 0 09:20:19 ? 00:00:00 /etc/syslogd
root 55 1 0 09:20:20 ? 00:00:00 /etc/sco_pmd
root 76 1 0 09:20:22 ? 00:00:00 strerr
root 43 1 0 09:20:19 ? 00:00:00 htepi_daemon /stand
root 1560 1 0 09:20:54 tty01 00:00:00 -sh
root 1561 1 0 09:20:54 tty02 00:00:00 /etc/getty tty02 sc_m
root 1562 1 0 09:20:54 tty03 00:00:00 /etc/getty tty03 sc_m
root 329 290 0 09:20:29 ? 00:00:00 (LPSCHED) PRINTER lpt5 PID
331 1
root 1563 1 0 09:20:54 tty04 00:00:00 /etc/getty tty04 sc_m
root 310 1 0 09:20:29 ? 00:00:00 rusersd
root 279 1 0 09:20:29 ? 00:00:00 /etc/cron
root 264 1 0 09:20:29 ? 00:00:00 /opt/K/SCO/lli/5.0.7a/bin/d
lpid /var/opt/K/SCO/lli/5.0.7a/dlpidPIPE
root 212 1 0 09:20:28 ? 00:00:00 htepi_daemon /u
root 303 1 0 09:20:29 ? 00:00:00 portmap
root 290 1 0 09:20:29 ? 00:00:01 /usr/lib/lpsched
root 422 1 0 09:20:32 ? 00:00:00 /etc/sshd
root 309 1 0 09:20:29 ? 00:00:00 rwalld
root 485 1 0 09:20:32 ? 00:00:00 statd
root 425 1 0 09:20:32 ? 00:00:00 /usr/lib/lpd
root 366 1 0 09:20:30 ? 00:00:01 /etc/in.prngd /etc/egd-pool
goldxf 331 329 0 09:20:29 ? 00:00:00 /bin/sh -c /usr/spool/lp/ad
mins/lp/interfaces/lpt5 lpt5-9296 goldxf "" 1 " " /u
root 494 487 0 09:20:32 ? 00:00:00 lockd -u 4 -t 16
goldxf 333 331 0 09:20:29 ? 00:00:00 /usr/spool/lp/admins/lp/int
erfaces/lpt5 lpt5-9296 goldxf 1 /usr/ibcos/UPC252
root 487 1 0 09:20:32 ? 00:00:00 lockd -u 4 -t 16
goldxf 339 333 0 09:20:29 ? 00:00:00 /usr/lib/hpnp/hpnpf -x lpt5
goldxf 340 339 0 - - 00:00:00 <defunct>
root 481 1 0 09:20:32 ? 00:00:00 biod 4
root 482 1 0 09:20:32 ? 00:00:00 biod 4
root 412 1 0 09:20:31 ? 00:00:00 /etc/snmpd
root 483 1 0 09:20:32 ? 00:00:00 biod 4
root 484 1 0 09:20:32 ? 00:00:00 biod 4
root 390 1 0 09:20:31 ? 00:00:00 /etc/inetd
root 444 1 0 09:20:32 ? 00:00:00 sendmail: accepting connect
ions
nouser 567 564 0 09:20:42 ? 00:00:00 /usr/lib/apache/bin/httpd -
d /usr/lib/docview -f /usr/lib/docview/conf/httpd.co
nouser 510 508 0 09:20:35 ? 00:00:00 /usr/lib/apache/bin/httpd
root 497 494 0 09:20:32 ? 00:00:00 lockd -u 4 -t 16
nouser 4726 508 0 14:59:38 ? 00:00:00 /usr/lib/apache/bin/httpd
root 498 494 0 09:20:32 ? 00:00:00 lockd -u 4 -t 16
root 499 494 0 09:20:32 ? 00:00:00 lockd -u 4 -t 16
root 500 494 0 09:20:32 ? 00:00:00 lockd -u 4 -t 16
root 508 1 0 09:20:35 ? 00:00:09 /usr/lib/apache/bin/httpd
nouser 511 508 0 09:20:35 ? 00:00:00 /usr/lib/apache/bin/httpd
nouser 512 508 0 09:20:36 ? 00:00:00 /usr/lib/apache/bin/httpd
nouser 513 508 0 09:20:36 ? 00:00:00 /usr/lib/apache/bin/httpd
nouser 514 508 0 09:20:36 ? 00:00:00 /usr/lib/apache/bin/httpd
nouser 557 556 0 09:20:36 ? 00:00:00 /usr/internet/etc/ncsa_http
d -d /usr/internet/admin
root 553 1 0 09:20:36 ? 00:00:00 /usr/lib/scosh/calserver
root 555 553 0 09:20:36 ? 00:00:00 /usr/lib/scosh/calserver
root 556 1 0 09:20:36 ? 00:00:00 /usr/internet/etc/ncsa_http
d -d /usr/internet/admin
root 559 1 0 09:20:41 ? 00:00:00 /usr/lib/scosh/caldaemon
root 564 1 0 09:20:42 ? 00:00:07 /usr/lib/apache/bin/httpd -
d /usr/lib/docview -f /usr/lib/docview/conf/httpd.co
root 1397 1 0 09:20:49 ? 00:00:00 /u3/gold6_syn/connect/vtxne
td -k67834 -p1958 log
nouser 1236 564 0 09:20:44 ? 00:00:00 /usr/lib/apache/bin/httpd -
d /usr/lib/docview -f /usr/lib/docview/conf/httpd.co
nouser 1237 564 0 09:20:44 ? 00:00:00 /usr/lib/apache/bin/httpd -
d /usr/lib/docview -f /usr/lib/docview/conf/httpd.co
root 1238 1 0 09:20:46 ? 00:00:00 /u3/gold6_syn/lm/synd
root 1373 1 0 09:20:47 ? 00:00:00 /u3/gold6_syn/dbl/bin/rsynd
-p 2330
nouser 865 564 0 09:20:43 ? 00:00:00 /usr/lib/apache/bin/httpd -
d /usr/lib/docview -f /usr/lib/docview/conf/httpd.co
root 1375 1 0 09:20:47 ? 00:00:00 /u3/gold6_syn/dbl/bin/rsynd
-w -p 2356 -u goldxf
gold 1521 1 0 09:20:51 ? 00:00:02 /u3/gold6_syn/dbl/bin/dbr /
u/gold/libs/NM_PST6.DBR
root 1564 1 0 09:20:54 tty05 00:00:00 /etc/getty tty05 sc_m
root 1542 1 0 09:20:54 ? 00:00:01 /usr/lib/samba/sbin/smbd -D
-s /etc/samba.d/smb.conf
root 1544 1 0 09:20:54 ? 00:00:03 /usr/lib/samba/sbin/nmbd -D
-s /etc/samba.d/smb.conf
root 1565 1 0 09:20:54 tty06 00:00:00 /etc/getty tty06 sc_m
root 1566 1 0 09:20:54 tty07 00:00:00 /etc/getty tty07 sc_m
root 1567 1 0 09:20:54 tty08 00:00:00 /etc/getty tty08 sc_m
root 1568 1 0 09:20:54 tty09 00:00:00 /etc/getty tty09 sc_m
root 1569 1 0 09:20:54 tty10 00:00:00 /etc/getty tty10 sc_m
root 1570 1 0 09:20:54 tty11 00:00:00 /etc/getty tty11 sc_m
root 1571 1 0 09:20:54 tty12 00:00:00 /etc/getty tty12 sc_m
goldxf 1650 1373 0 09:25:34 ? 00:00:08 /u3/gold6_syn/dbl/bin/rsynd
-p 2330
root 6128 390 0 16:59:52 ? 00:00:00 telnetd
nouser 5005 508 0 15:59:37 ? 00:00:00 /usr/lib/apache/bin/httpd
goldxf 4643 1373 1 14:34:47 ? 00:00:05 /u3/gold6_syn/dbl/bin/rsynd
-p 2330
goldxf 4880 1373 0 15:31:36 ? 00:00:10 /u3/gold6_syn/dbl/bin/rsynd
-p 2330
root 6129 6128 2 16:59:52 ttyp0 00:00:00 -sh
goldxf 3478 1373 0 11:42:24 ? 00:00:02 /u3/gold6_syn/dbl/bin/rsynd
-p 2330
goldxf 4751 1373 0 15:06:14 ? 00:00:01 /u3/gold6_syn/dbl/bin/rsynd
-p 2330
root 6176 6129 2 17:01:22 ttyp0 00:00:00 ps -ef
goldxf 1679 1373 0 09:28:03 ? 00:00:05 /u3/gold6_syn/dbl/bin/rsynd
-p 2330
root 6174 1542 0 17:01:13 ? 00:00:00 /usr/lib/samba/sbin/smbd -D
-s /etc/samba.d/smb.conf

Tom Dunne

Now we are getting somewhere.

All the entries starting with goldxf I would guess, are the application.

Looking at this line:

gold  1521     1  0 09:20:51       ?    00:00:02 /u3/gold6_syn/dbl/bin/dbr /
u/gold/libs/NM_PST6.DBR

...it looks like the gold username (I'd say it's safe to assume this is the application also) and is using a the NM_PST6.DBR file. A quick google on DBR files gives two possibilities : Comdial VMMI Database or DB/TextWorks Database Personally, I would think it is the latter, though it says nothing about Linux on the DB/TextWorks website.

It might be worth poking around the /u3/gold6_syn directory looking for a log file of some shape or description to see if it gives you anything.

Snowbat

Looks like it uses a Synergy/DE proprietary database

Ask your supplier how the Direct report and data export to Microsoft Excel feature works or look at using xfODBC to pull the data into (eg.) MS Access. From Excel or Access you can export to CSV for import into the new system.

pablo21

Snowbat wrote: »

Looks like it uses a Synergy/DE proprietary database

Ask your supplier how the Direct report and data export to Microsoft Excel feature works or look at using xfODBC to pull the data into (eg.) MS Access. From Excel or Access you can export to CSV for import into the new system.

THats the one allright! The direct export is just the ability to export the contents of whatever screen your looking at to excel and the results of some reports can be sent there also i.e. work in progress, parts, wholegoods etc.
Am I stuck with pulling a load of individual reports and trying to merge them?

Snowbat

A database usually consists of a number of individual tables. Some of the tabels may relate to information in other tables:
http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=50&rl=1

To migrate the database, you'll need to recreate each table on the new system and load each table with its data. When creating the tables, make sure to set an appropriate data type (and length) for each column - eg. date, mediumint(9), char(200), varchar(5000), decimal(10,2).

Also check if any commas exist in the data before exporting to CSV - if they do you'll need to either replace them with a different character or chose something other than comma as your delimiter (pipe '|' can be a better option and less likely to be present in data than commas but do check first).

Jackz

Very suprised that Kerridge/ADP don't have somebody who can figure out how to either export the data to something they can use or else at least get on this box and tel you what way it stores data. Could be simple flat files. I do work for a motor retail group in the uk and Kerridge do this kinda thing all the time when they buy a garage its in their interest the get paid professional services fees and more licensing revenue from the increase in users.

It possible that your current software vendor doesn't have an easy export feature either because the way the data is stored is very simplistic or its just not in their interest for you to easily take your data and move to another vendor.

Their are companies who specialise in extracting and coverting data in situations like this.

Im suprised again about Kerridge not having a look at the back end and also suprised they havn't had a customer move from this vendor to them in the past.

pablo21

Jackz wrote: »

Very suprised that Kerridge/ADP don't have somebody who can figure out how to either export the data to something they can use or else at least get on this box and tel you what way it stores data. Could be simple flat files. I do work for a motor retail group in the uk and Kerridge do this kinda thing all the time when they buy a garage its in their interest the get paid professional services fees and more licensing revenue from the increase in users.

It possible that your current software vendor doesn't have an easy export feature either because the way the data is stored is very simplistic or its just not in their interest for you to easily take your data and move to another vendor.

Their are companies who specialise in extracting and coverting data in situations like this.

Im suprised again about Kerridge not having a look at the back end and also suprised they havn't had a customer move from this vendor to them in the past.

Kerridge have had somebody in here already and on the server who tried but had no luck converting the data, thats what brings me here!

Jackz

>file /u/gold/libs/NM_PST6.DBR show anything interesting?

pablo21

Jackz wrote: »

>file /u/gold/libs/NM_PST6.DBR show anything interesting?

Jack I've zero knowledge of Unix I'm afraid but there is a file there under that name. Is that the principle database file? Any ideas on how to extract information from it?

Jack if your knowledgeble in this specific area I'd be interested in talking to you. PM me if you are!

Jackz

I am by no means a data conversion specialist, I was just suggesting that running the command file followed by the filename might show the file type.

DublinWriter

pablo21 wrote: »

The DMS system is IBCOS Gold with a windows interface.
All help greatly appreciated. NB I know nothing about Unix I'm afraid.

I had a quick peek at the IBCOS site and they state their software is fully ODBC compliant.

Basically what you'll have to do is contact them for the ODBC drivers to install on your PC. With those installed you can pull out the raw data using a tool such as Crystal Reports to create your text/CSV files on your PC.

pablo21

DublinWriter wrote: »

I had a quick peek at the IBCOS site and they state their software is fully ODBC compliant.

Basically what you'll have to do is contact them for the ODBC drivers to install on your PC. With those installed you can pull out the raw data using a tool such as Crystal Reports to create your text/CSV files on your PC.

I've just emailed them with a request for same! I'll let you know how I get on..

pablo21

Jackz wrote: »

I am by no means a data conversion specialist, I was just suggesting that running the command file followed by the filename might show the file type.

Jackz, many thx for you help, I'm completely out of my depth with Unix!

pablo21

My only option for converting the data is to get IBCOS to do it for me at STG£500 per day and they'll need to do both a trial and go-live conversion so about STG£2000 in total! Robbing %"£&%!"$&$!*& :mad:

Snowbat

I found these guys while Googling for info on xfodbc. They might be able to extract your data and prep it for import into your chosen database system for a more reasonable fee?
http://www.camdensoftware.com/Technologies.htm

From what I can see, xfodbc was bundled with patches for Synergy/DE 8.1.7a and later - you can get it from synergex.com but it looks like you need to have a helpdesk or support contract with them to gain access.

p2kone

how did you get on with exporting the data from gold?

pablo21

I just paid Ibcos to do it for me, wasnt expensive, about 450 pounds I think.

p2kone

did ibcos just dumped all the database tables into excel files or somthing similar?

pablo21

Aye exactly that, output to csv abd that was it!