Oct 30 Bulk Loading Oracle without SQL*Loader | Part 2 – Loading Data into Oracle with ODBC Driver As you've done with DB2 in part 1, you need to configure a Progress DataDirect ODBC Driver for the target database, which is Oracle. Enter all of the connection information for the target Oracle database. Do a “Test Connect” to ensure no fat-fingering. Not surprising, you need to supply your user name and password. Assuming no typos or errors, all should be ok. Now to the real meat of the loading operation. Click on the “Bulk” tab in the Oracle ODBC driver configuration pane, again, mirroring what you did with DB2. Now, rather than exporting data, you want to prepare for loading. You could go directly to the “Load Table…” button, but let's be safe and verify the exported DB2 data against the target Oracle database before actually attempting to load it. Clicking the “Verify…” button presents you with a dialog where you select the configuration file (the .xml file) associated with the DB2 “Export Table” operation. You're going to choose the one generated when you exported the ASSIGNMENTS table, the ASSIGNMENTS.xml file. Specify the target Oracle table name and the configuration file, and choose “Verify”. (While the DB2 and Oracle tables have the same name here, they do not have to.) After clicking the “Verify” button, you will be prompted to enter your ID and password for the target Oracle system. The .xml metadata file, which was built from the source DB2 database columns, will be analyzed against the column definitions of the target Oracle table, to see if there are any inconsistencies or mismatches. IMPORTANT – The target table must already exist. Assuming all is in order, you'll see the following. Note that completion of the verify process does NOT mean that all is ok. It simply means that the operation ran to completion. It's the difference between taking a test, and passing one. Even though the “verify” operation completed, some potential problems were detected. You're alerted that the verification process has encountered a possible problem. Turns out to be a possible code-page conflict, in the TASK column. Not flagged as an outright error, just a potential problem. Consider it an “FYI” or “Heads up”. The warning isn't anything that concerns you, so, you now initiate the actual loading into Oracle. Click the “Load Table…” button on the Bulk pane of the ODBC driver. All of the information required for the load has been carried over from the “Verify” operation. The name of the target table, the locations of the ASSIGNMENTS.csv data file, the ASSIGNMENTS.xml metadata file, and the log file where details of the operation will be recorded. Continuing on. Clicking the “Load Table” button will again prompt you for your target Oracle database credentials. AND THAT'S IT! No complicated applications or configuration files. If you've been successful up until this point, you should see this. You can validate this a number of ways. You can go and do a “SELECT * from ASSIGNMENTS” on your Oracle system to see that the data is there. You can also go to the ASSIGNMENTS.log file, and see what has been recorded in the log. 45 rows successfully loaded! That's all there is to it. By simply using a few additional fields in the Progress DataDirect Connect for ODBC drivers, you've been able to easily move data from DB2 on z/OS to Oracle! No complicated command line utilities or expensive, difficult-to-learn applications.
Oct 29 Bulk Loading Oracle without SQL*Loader | Part 1 – Extracting the data from DB2 In this post, we’ll focus on the extraction of the data from the DB2 system. You’ll learn how to configure your machine to easily extract data from DB2 on z/OS in preparation for a bulk load without running any command-line utilities or writing any SQL! Requirements: A current Windows machine, either 32-bit or 64-bit. I’ll actually be using a Windows 7, 64-bit Ultimate configuration, running under VMWare Fusion on my Macbook Pro. (Instructions for configuring VMWare Fusion and Windows 7 are available at the VMWare Fusion Blog . Runs VERY well, by the way!) Go to http://www.datadirect.com/products/odbc/index.ssp to download and install the appropriate version of Connect for ODBC. Here’s what your ODBC Administrator Control Panel should look like after the installation. If you already had drivers installed, you may see additional entries. Not a problem. Not a Microsoft Windows fan? You can also do this via our ODBC drivers for Red Hat or SUSE Linux, available from the same web address . We provide an optional GUI administrator on those platforms that allows the exact same operations as shown here on Windows. Your source is your operational data in DB2 on the mainframe. Note that the Progress DataDirect DB2 drivers connect to DB2 on the mainframe WITHOUT any intermediate tier, such as DB2 Connect or any client libraries. It’s very similar to a Type 4 JDBC driver. A self-contained driver that connects to the server by IBM’s DRDA protocol. So a connection will be established directly from the Windows machine to the mainframe. Back to the setup – Select the DB2 driver to configure a new data source. Someone knowledgeable about ODBC may recognize that this is not your father’s ODBC driver for DB2! Many more options and choices. But the fundamentals should be familiar. The IP address, the port that the database is listening on, etc. You must specify the info that is needed to adequately identity the DB2 system that you want to connect to and subsequently extract the data from. Fill in the relevant info, and hit the “Test Connect” button (not a real requirement, but it provides immediate feedback that our configuration is correct). This will bring up a login panel that will attempt to connect to the database as defined, with your User ID and Password. Success! If all of the configuration info and credentials were in order, you should see the following. So you know that you can successfully sign into your source database. You’ve probably noticed that there are a variety of tabs in the setup pane. These aid in the configuration of the various features available with the driver. You want to configure the “Bulk” feature. Clicking on the “Bulk” tab changes the Setup pane to configure one of our newest enhancements. Bulk support is a feature newly implemented in this most recent release. The “Enable Bulk Load” checkbox is prominent in this pane. What does it do? It allows an ODBC application that is coded using parameter array batch inserts to use the database’s bulk protocol WITHOUT RECODING. But that’s not what this problem calls for. The checkbox has no effect in this particular problem. To transfer data from one database to another via the ODBC driver, you are more interested in the three buttons in the lower half of the pane – “Load Table…”, “Verify…”, and “Export Table…” Specify the table where your data resides by using the “Export Table…” button. (You would repeat this operation for each DB2 table with data you need to move into Oracle.) You want to copy data from the ASSIGNMENTS table in DB2 over to Oracle. The table name needs to be directly entered; there is currently no way from this dialog to browse the database to determine the desired table. Sounds like a good enhancement for a future update! You are going to specify the location of a file to store the DB2 data in a .csv format. A .xml file will also be created, describing the data types present in the table. The driver will also create a .log file, detailing the results of the operation. Now, “Export Table”! You are prompted for your DB2 login credentials. If there are no hiccups, you should see that the export was successful! Visit the file system location (in my case, the BulkBlog folder) that you specified earlier in the “Export Table…” pane, and you’ll see the three files. The .csv file which contains the data, a .xml file which contains the metadata about the data types in the .csv file, and the .log file, which details what happened during the export. The ASSIGNMENTS.csv file is just a .csv file, nothing unique. It can be opened in any application that supports .csv file formats. Here, it’s viewed with the Windows 7 version of WordPad. No surprises. Similarly, the ASSIGNMENTS.xml file can be viewed by any application that can open well-formed XML. Internet Explorer 8 will serve in this case! The different column names and their associated data types are detailed here. Lastly, the ASSIGMENTS.log file. Notepad works fine! This was a rather simple operation, not really a large amount of data, by any means. The most important things to note – The number of rows read matches the number exported. No errors, so no failed rows! SUMMARY
=======
Your DB2 data is ready to be loaded into Oracle. Note that the extraction process is entirely independent of the target. What you’ve done so far has involved NOTHING specific to the target database. This data could just as easily be destined for SQL Server or Sybase or even another DB2 system. The extraction phase is completely distinct from the load phase. In part 2, you’ll learn how to load this data into Oracle in a few simple steps, with nothing more than an ODBC driver!
Oct 28 Bulk Loading Oracle without SQL*Loader | An Overview You have an ongoing requirement to move data from one database to another. In this case, your operational data is in DB2 database running on z/OS on the company mainframe. There is a business need to periodically copy the data to an Oracle database. (The DB2 system is “off-limits” when it comes to ad hoc reporting.) There are many excellent ETL (Extract, Transform, and Load) tools on the market that might be considered – open source and closed source, both commercial and free. Whether free or $$$, they often take substantial investments in time and skills to get working. There are a number of SQL desktop tools that might help extract data from one system, put it into some type of intermediate file, and push it into another. But these tools typically can’t take advantage of the high-performance capabilities of the ETL tools, or the brand-specific loading tools, like Oracle’s SQL*Loader utility. They just do traditional database inserts either via ODBC or JDBC. Well, then why not simply use Oracle’s SQL*Loader? Much easier said than done. You’ll be faced with control files and command lines, and that’s just the “load” part. What about getting the data out of your DB2 system, properly prepared for loading into Oracle? Is there a DB2 “unload” utility? What if you could move your data from DB2 to Oracle without buying an expensive application? What if you could avoid having to learn a complicated application? What if you could move your data by simply pointing-and-clicking, by simply configuring a Windows Control Panel? Extracting and inserting your data by using nothing more than an ODBC driver? Yet, still get support for Oracle’s bulk protocols for maximum performance? With Progress DataDirect Connect and Connect64 for ODBC , it can be that easy! How would you do this? Install a couple of ODBC drivers, click a few buttons, and you’ll have copied data from DB2, and loaded your data into Oracle! Seriously! What's next? Over the next two days I will publish a two part series. Part 1 will explain the ease at which you can extract data from DB2, and part 2 explain loading this data into Oracle in a few simple steps, with nothing more than an ODBC driver!
Oct 2 Progress DataDirect Brings its Successful Data Connectivity Workshops to New International Cities th.. Progress DataDirect recently hosted successful workshops in South and North India and has decided to extend the well-received workshop agenda to new cities this October. The Data Connectivity Workshops covered various aspects of DataDirect’s well-established data access middleware that offers seamless connectivity between heterogeneous clients and heterogeneous data sources. These products ultimately decrease development and operation costs, while allowing for greater interoperability. Many aspects of ODBC, JDBC and ADO.NET technologies were discussed including rarely explored topics of scalability, CPU efficiency and memory footprint of database drivers and data providers. The sessions also covered various built-in functionality found in the DataDirect Connect family of products such as application failover and enterprise security features , such as Kerberos , SSL encryption , and unique secure architecture embedded in both drivers and providers. Bulk load functionality now available in DataDirect Connect is supported directly by the various APIs, allowing bulk load operations to be managed by an application and not be handled by external processed will be discussed and demonstrated. Progress DataDirect products exponentially increase the availability, capabilities and security of distributed applications for both corporate customers who can standardise on a single provider of database connectivity components and software development companies that can embed DataDirect components within their applications reducing the footprint of their overall application and ensuring that database access is provided to their application providing full coverage of all currently available popular database systems. At the September workshops in India organisations and software companies learned about the benefits from the high-performing and flexibility of DataDirect products. During the workshops there were engaging discussions between attendees and company thought-leaders about how the various technologies had been used successfully by existing companies and software houses. Also a number of questions were raised about other possible uses for the technologies. Attendees shared they gained valuable knowledge regardless of their starting point at the beginning of the workshops. The next Data Connectivity workshops will be held a various locations in Europe, Eastern Europe and the Middle East in October 2009. We hope to see you there! More information can be found at http://www.datadirect-tech.co.uk/connectivity.html
Aug 7 Easily Move Large Amounts of data with Bulk Load! Many companies today are moving mountains of data – some are moving them into data warehouses to run reports, moving for duplication/archival, and more often than not, the design of these systems is accidental. Traditionally there has been no standards based way for moving these large amounts of data between systems, resulting in poorly architected solutions that suffer when additional systems come online or additional databases are thrown into the mix. We have now brought this technology into the ODBC, JDBC, and ADO.NET space, unlocking a standards based bulk loading mechanism that will outperform the vendor provided bulk loading tools, allowing you to easily and quickly move large amounts of data into data warehouses and other systems in a way that will grow with your company’s needs! We have recently recorded a webinar that covers real world scenarios and showcases a demonstration of our Bulk Loading in action using both our JDBC and ODBC drivers! We move data from Oracle to DB2 with ease and even showcase a way to move millions of rows of VSAM data into Oracle with only 5 lines of Java code! Take a look at the webinar recording and see how you can benefit from this technology in your environment! To view the webinar recording at your leisure, follow the link below. http://streaming.progress.com/prc/datadirect/dd_moving_mountains/index.htm A link to the slides used in the webinar are below: http://img.en25.com/Web/DataDirect/Bulk%20Webinar%20-%20Moving%20Mountains%200709.pdf -Jesse
Apr 30 ODBC Is Not Dead! I enjoy watching the show MythBusters because I love the concept of testing out whether a belief that people collectively accept as fact holds up under scientific scrutiny. After watching the show, I find myself thinking more critically about whether the things I believe are true based on proof or myths based on blind acceptance of “prevailing wisdom”. I bring this up because it's time for me to revisit and put to rest one of my favorite myths of the application software development community – that ODBC is dead. The people who say that ODBC is dead haven't taken its pulse recently. Despite claims that ODBC usage has been eclipsed other data access APIs, the fact is there are many critical applications that still use it. There is even some degree of new application development being done with ODBC through languages such as PHP and Perl. Even though Microsoft is currently pushing alternatives data access APIs such as ADO.NET, they still seem to think there is enough of a market for ODBC to announce a beta release of ODBC 3.8 , which adds features such as asynchronous execution, streamable output parameters, and ODBC C data type extensibility. Of course, we at DataDirect have always known that ODBC isn’t dead . Every day we have new customers come to us seeking the very best ODBC drivers for new development projects as well as existing, critical application deployments. Despite the fact that it has been years between updates to ODBC, DataDirect has continued to innovate in ways that push the boundaries what defines an ODBC driver, yet work seamlessly with any ODBC compliant application. Over the next few weeks, I will outline just a few of the many features and innovations we have added to our ODBC drivers. These features not only redefine what an ODBC driver is, but also shatter perceptions about the limits of ODBC applications. With any luck, what I share will demonstrate that reports of the death of ODBC have been greatly exaggerated and thus we can all declare this myth “busted”.
Jun 23 Sharing Your Love for Animals, One Bite at a Time If you are looking at the picture above and thinking to yourself, “what does a box of cereal have to do with software,” then you might need to spend more time looking at life the way George Carlin did. Carlin, who passed away yesterday, built a career of pointing out the amusing side of stupidity and the ironies of life in our times. I imagine that the box of cereal pictured above would have made an all-too-easy target for his barbed wit. Wish you were still here to toss out one last rant, George. While walking through the grocery store yesterday I came across a box of this product. Made by Kellogg's , and promoted by / cross-marketed with the cable TV station Animal Planet , “ Wild Animal Crunch ” comes with a variety of covers. I saw ones picturing sea lions (not seals because of the presence of external ear flaps) as well as polar bears but apparently there also ones featuring meerkats and panda bears . What I found fascinating about this was the combination of both the name of the product as well as the picture shown. I'll allow that the sarcastic smart aleck demographic probably isn't one that Kellogg's was targeting for this product, but nevertheless, I can't be the first consumer to see this and think that this might be a subject of future marketing classes on how not to market your product, much some of the urban legends surrounding the sale of Gerber baby food in locations in Africa . Keeping this in mind, I read the box and found it increasingly hilarious to read things such as, “Naturally and artificially flavored and vanilla-chocolate whole grain cereal” (what, wild animals need to be sweetened to be doused in milk and eaten with a spoon?), “A good source of fiber” (presumably because they didn't remove any cute, fuzzy hair prior to processing), and my personal favorite, “Share your love for animals” (by eating them, evidently). I have to think that somewhere the folks who came up with the “ People Eating Tasty Animals ” take on the acronym PETA are licking their chops at the prospect of following through on this last exhortation by passing the box around the breakfast table. I'm also curious about how Kellogg's chose the 4 animals that grace the cover of its boxes – apparently they feel that their target market finds the idea of eating something with cute, fuzzy animals appealing. At first blush, it would seem that those who would be most likely to respond to this marketing would be: Meerkats – Animal Planet show hosts and cameramen tired of just idly sitting back while “nature takes its course” Panda bears – Vegetarians. Panda eat an exclusive diet of bamboo, after all. Polar bears – Oil company executives. Sea lions – Killer whales (bit of a niche market, but Kellogg's should have this one cornered with this master-stroke of product and positioning) I gave it some thought and came up with some other wild animals that might help them expand their market segments a bit: Bald eagles – Eat this cereal if you are patriotic. Don't and you're a terrorist. Tapeworms – “The more you eat, the less you weigh.” Offers the possibility of some cross-promotional opportunities with GNC and CBS's Survivor TV show. Sea sponges – Yes, sponges are animals . The biologists out there will be all over this one for the geek quotient alone. Dung beetles – Anyone willing to eat a cereal with one of these cuddly guys on the front would eat pencil shavings, which brings me to my final comment… Regarding the taste of Wild Animal Crunch – Kellogg's should look into some better tasting animals. The sea lion variety was a bit like a bad cup of cafeteria coffee – not enough flavor as I ate it but plenty of sweetish residue left in my mouth that I kept tasting for the next half hour even after vigorous toothbrushing. The poor flavor yet distinctive texture conjured up all sort of unpleasant thoughts about what part of the sea lion lent itself to staying crunchy in soy milk. Perhaps the Panda bear will provide redemption for Kellogg's self-anointed role as wild animal breakfast gourmet. I'm practically vegetarian anyway.
Jun 10 SOA What I’m Saying Is, Data Access Matters You may be wondering what the picture at right has to do with the subject of this blog entry. The answer: nothing. It's just a humorous photo that made me smile this morning. I mean, how often do you see kids these days pantomiming Snidely Whiplash ? Anyway, on to the real reason for this entry: shameless self-promotion. My article, Solving Data Access Problems in SOA Environments , has been published by ITarchitect, a UK-based online publication. It has also been published in print in ITarchitect's, sister publication, VSJ . The article covers much of the same ground as the presentation that I gave at the MySQL 2008 Conference & Expo ( the one that I blogged about last month ). Basically, if you are looking for more explanation wrapped around the slides in my presentation, then this the article to read. I am also told that insomniacs can also find something of value from reading my article. Essentially, if you are software architect who can't sleep, my article should be considered required reading. Perhaps an even better article on the same subject can be found at SOAWorld Magazine. Here we see a smart gentleman named John Goodson writing on the subject of Best Practices for SOA: Building a Data Services Layer . What makes him so smart you ask? Well, short of reading the article and figuring out why for yourself, you can always fall back on the fact that he has managed to keep me gainfully employed – in my world, that counts for something (even if it doesn't exactly make me an unbiased reviewer). ^_^ Technorati Tags | | data+access data+services data+services+layer SOA John+Goodson
Jun 9 Oyez, Oyez! New Entities Supported by DataDirect The image of the herald is one that pervades Western culture. The Biblical image of the archangel Gabriel announcing the forthcoming births of both John the Baptist and Jesus is one that Christians are very familiar with. I'm not much of a herald. I'm hardly angelic, and I don't own a horn much less own one. I think I would have made a great town crier , though. I've been told that my voice carries – over cube walls, through doors, down hallways, across vast expanses, etc. I'm reasonably sure that being told that I have a voice with such a unique gift for being heard above the din of an office setting is a compliment. Isn't it ? All of this self-analysis brings me to the reason for my post today: my self-appointed role as town crier for news and announcements of interest to my loyal readers. To you, my faithful minions, I thrice toll my bell of harkening and lift my voice to loudly proclaim: DataDirect to Support ADO.NET Entity Framework for Oracle Data Sources Admittedly, this announcement is anti-climatic, for those of you who read this posting on the Microsoft blog last week, but it does bear repeating. There is a lot of interest right now among developers in the ease-of-use of LINQ and the productivity of the Entity Data Model. Our announcement is meant to reaffirm that LINQ-based and Entity Data Model-based applications will experience the same unique benefits of using DataDirect's Connect for ADO.NET providers for relational database access as ones built to use current technologies such as DAAB . (Sorry for that segueway, but you know I've got to give a shout out to now and again in order to pay the bills ^_^) While this is hardly earth-shattering news or something with the weightiness of, say, a royal proclamation, it is one that we're proud to make because it's a signal of where our products are headed…sort of like a graduation announcement. It's enough to make a tough guy like me misty-eyed and emotional – it's like I'm witnessing a precocious little tyke growing up right before my eyes! *sniffle* That's all for now. I'm off to hum a few bars to warm up for my daily auditory assault of my co-workers. Your comments and feedback are welcome so long as it doesn't compete with the “ 11 ” setting of volume knob of my strident self-important braying.
Technorati Tags | | ADO.NET DAAB Entities Entity+Framework LINQ Microsoft Oracle
Jun 6 Three Shouts: Virtualization, Data Services, and Beer With apologies to Tears for Fears, it's time for me to shout (not a RickRoll) about a few items that I think deserve some attention. First and foremost, my esteemed colleague Mike Johnson has written an excellent article entitled The Importance of Data Connectivity to Virtualization . Rather than being a tribute to a cleverly written play by Oscar Wilde , Mike's article presents the case for choosing high quality data connectivity components in order to maximize an investment in virtualization. Second, if you haven't yet signed up to attend Data Services World , I would recommend you get off of your duff and do so. The conference, scheduled for June 24, 3008 at the Roosevelt Hotel in New York, features a keynote delivered by John Goodson , an industry luminary in data connectivity and someone that I report up to. Anyone interested in learning more about data services should attend as John is as excellent a speaker as he is a generous boss. ^_^ Third, and finally, I'd like to call attention to beer that I had recently, a 2007 Abyss from Deschutes Brewery out of Bend Oregon. For any of you who like your beer strong, dark, and complex, I would strongly recommend you find a bottle of this delicious Imperial Stout to try. If you don't – feel free to ship me another bottle so I can enjoy this beer all over again. Slàinte! Technorati Tags | | data+services John+Goodson Mike+Johnson virtualization
