I'm a recent college graduate entering in the " real world" and boy let me tell you it can quite challenging. I just got a job with a large fortune 50 company lets just say that my expectations and tasks have expanded since I first got hired. Last week I got assigned a pretty big task and I could use some help.
I have a fairly large access database and I need to convert the SQL queries to AIX SQL. All information is pulling from AIX but ms access makes the information look nice and pretty and has an easy interface. The problem is latency. Because the database is so large, the queries take a while to run.
I guess this is my fault because in school I learned that MS access has and gives a SQL view and I had an intro to linux/unix and I simply just made a suggestion to convert the queries to AIX. 3 weeks later I get this seemingly overwhelming task to convert this database to AIX. I have about 326 queries that I need to convert.
Is there a tool or an easy way to do this or do I need to do each on by hand?
Do you have any information or links that I could benefit from on how to convert MS access SQl to AIX SQL?
Some queries are simple and only involve 2 or 3 tables while others are very complex.
Please let me know where I can start?
I really really appreciate everyone's help.
Am I missing something here?
MS ACCESS to me run on Windows servers ... Im surprise it is not SQLServer you have, and I have not heard of AIXSQL, I know DB2 Oracle I used to use Sybase and Informix, and you can have MySQL...
Where do the SQL requests get executed? I always though it was where the RDBMS resides which would be on the Windows server so why convert?
Are you sure you were not asked to migrate the MS ACCESS database to a RDBMS supported on AIX?
vbe,
I'm sorry to confuse you. Maybe if I give you a visual, it might explain a little more clearly what I need to do. Below is my MS access SQL query statement:
qryctnsReceived
SELECT Sum((dbo_e538a_rcv_dtl!rcv_qty_amt)/(dbo_item!case_qty)) AS Ctns
FROM dbo_e537a_rcv_hdr INNER JOIN (dbo_e538a_rcv_dtl INNER JOIN dbo_item ON dbo_e538a_rcv_dtl.t024_itm_nbr = dbo_item.itm_num) ON (dbo_e537a_rcv_hdr.e058_po_nbr = dbo_e538a_rcv_dtl.e058_po_nbr) AND (dbo_e537a_rcv_hdr.e537_rr_nbr = dbo_e538a_rcv_dtl.e537_rr_nbr) AND (dbo_e537a_rcv_hdr.t063_lct_nbr = dbo_e538a_rcv_dtl.t063_lct_nbr)
WHERE (((dbo_e538a_rcv_dtl.upd_dm) Between GetStartTime() And GetEndTime()) AND ((dbo_e538a_rcv_dtl.rcv_qty_amt)<>0) AND ((dbo_item.case_qty)<>0) AND ((dbo_e537a_rcv_hdr.ord_typ_cd)<>4) AND ((dbo_item.itm_class)<>758));
I've already started working on this project just to get a head start. I've massaged the code so it will work with in AIX and the following code is below. To clarify, I have DB2 on AiX and I need to convert MS access SQL queries to DB2 queries within Aix. I converted the above code below to this:
NAME[1]="Receiving Reg Ctns"
QUERY="SELECT Sum((e538a_rcv_dtl.rcv_qty_amt)/(item.case_qty)) AS Ctns"
QUERY=$QUERY" FROM e537a_rcv_hdr INNER JOIN (e538a_rcv_dtl INNER JOIN item ON e5
38a_rcv_dtl.t024_itm_nbr = item.itm_num) ON (e537a_rcv_hdr.e058_po_nbr = e538a_r
cv_dtl.e058_po_nbr) AND (e537a_rcv_hdr.e537_rr_nbr = e538a_rcv_dtl.e537_rr_nbr)
AND (e537a_rcv_hdr.t063_lct_nbr = e538a_rcv_dtl.t063_lct_nbr)"
QUERY=$QUERY" WHERE (((e538a_rcv_dtl.upd_dm) Between ('"$STARTTIME"') And ('"$EN
DTIME"')) AND ((e538a_rcv_dtl.rcv_qty_amt)<>0) AND ((item.case_qty)<>0) AND ((e5
37a_rcv_hdr.ord_typ_cd)<>4) AND ((item.itm_class)<>758))"
QUERY[1]=$QUERY
After much trial and error, I finally got it to work. I've been working on it all day, trying to figure out what I need to do. When looking at the above code, I believe this is a DB2 SQL statement. I just have 325 more to convert and I thought that maybe there was an easier way than each individual query manually. So to highlight my changes from the above code to this new code--> I removed the "dbo_" prefix on all my tables-->I replaced all my "!" exclamation marks with a period "."-->and date functions had to be converted to text. . .
You find me as dumbfounded as vbe: "SQL" (structured query language) is a quasi-standard for manipulating data in a relational database and most relational databases - Oracle, DB2, Informix, MySQL, ... support it.
AIX is an operating system. You can install a relational database software onto it (this has been done perhaps millions of times) and - if that database software supports SQL - you can run SQL-statements against it. But AIX is NOT a relational database itself and it won't understand SQL queries to the same amount as native Windows: not at all. You cannot "convert SQL queries to AIX", because there is nothing in AIX which will understand them.
When - if - you mean to isntall some SQL-capable DB software onto AIX and convert the Access-SQL to this: this might work, by and large, but Mickeysoft built in some nasty surprises for you into its so-called SQL. In fact there are some severe differences between the SQL standard everybody adheres to and what makes MS makes you believe is SQL but in fact isn't. Here are some links:
/PS: sorry if i sound somewhat bitter, but half of my life i have to explain to some clueless victim of M$$'s marketing machinery that what Mickeysoft calls "X" is not anywhere near "X" but in fact some downgraded, lobotomized version of "X" with some useless and clueless extensions of their own which is doing what is supposed to be left over from a vandalized standard in a different and incompatible way. For "X" put, in no particular order: TCP/IP, HTML, SQL, SCSI, Java, .....
I might be disgruntled but i think i have earned my entitlement to it.
Thank you so much for your replies. Your comments have directed me to ask more specfic questions to my leadership team. This has been helpful to me and has provided some clarity to the scope of my project.
Please forgive my ignorance. After further inquiry, there is sybase software that is ruuning within the AIX OS. I hope this helps provide the clarity you need.
My original question is, How can I convert MS Access SQL queries to Sybase SQL queries efficiently?
Are there any programs out there that can assist with this conversation?
OK, Sybase it is. Be aware that this forum deals with AIX and its workings itself. If your question would be "how can i install Sybase version X in AIX" we might have some answer for you, but to understand how exactly Sybase does what it does is usually not within the scope of our job. In most cases i get some specs, install a machine, maybe install the database software (in my case mostly DB/2 or Oracle) onto it and then give the system to the DBAs (database admins) to do whatever they do. The next time i do work on the machine it is either some administrative thing: new users, enlarge filesystems, such things - or for performance tuning. For all this i have to have a cursory knowledge of what the database software does and how it does it, but not more.
What i want to say with this rather lengthy preamble is: in things database most (perhaps all) of us are not experts but "interested laymen". You should take that into account and double-check with other sources.
I don't know of any program which does that but - see above - if such a program would exist i probably wouldn't know it anyway. On the other hand i do not think you will need a program at all. Get some DB-programmer and let him read over the code. He should be able to find all the non-standard pieces in it. Then he can change these pieces to standard-conformant syntax/grammar. He doesn't even have to have in-depth knowledge of MS-Access, because he can check any unknown construct in the documentation of Access. he should have a good knowledge of the target platform, though.
All that you need to know if that you want to migrate data from Access to a SQL compliant database. The easiest way to do that would be to put a Sybase ODBC connector on your windows machine. Then you should create a schema in Sybase for your data, give that schema the ability to create tables and indexes, which you probably have a Sybase database administrator that will do that for you. You need a Sybase client to log into Sybase and create tables that are identical to the tables in Access. So you will need to map the data types between the Sybase database and Access. There should be an online resource to do that. Rename each of the local Access tables to <table>_save, then use ODBC to link each of the new Sybases tables to the Access database. Insert all of the data from each saved table to the corresponding table in Sybase. Now when someone opens a form they are getting the data from Sybase.
You will need to either give everyone the same username and password, or create individual users with synonyms that point to the tables they need and that puts your data in Sybase where hopefully it will get backed up. You will likely need to create indexes, make sure that every table has a primary key and probably create foreign keys between parent and child tables. Now you are a DBA...
This is generally correct, but in detail one might experience problems. For instance: if i remember correctly Access treats undefined logical values as FALSE, standard SQL defines them as being "undefined". If your SQL query relies on this it might be syntactically correct but still not doing what it should.
That is a good point, but this is precisely where the testing would come in. Since the OP will have renamed the original tables in the Access database, he can take several attempts at migrating the data. Plus in Access you can create functions that will translate undefined values to something else. I haven't done much Access lately but I thought that Access handled undefined data as an empty string rather than null. Either way testing will be important as well as migrating the data a number of times. But if this is your job, you have time to try it over and over until its right.
