Iseries Database edit

Posted on 2009-12-17
Last Modified: 2013-12-06
  I am trying to edit a couple of rows in a table with iseries navigator. problem, there are thousands of rows. I might be crazy, but I cannot find any search funtion to find the rows that I would like to edit. Is a SQL script the only way I am going to be able to change this? Is there a better editing tool out there?

Thank you
Question by:mfleming07
    LVL 18

    Accepted Solution


    SQL scripts are definitely the best way to go, but there are a few other alternatives.

    I know there's a third-party tool called DBU that does this pretty well, but (of course) it costs moeny.

    Alternately, you could StrDfu (from the green-screen) and use the option to "Update data using temporary program". But, while that does work, it's pretty clumsy.

    IMHO, sql scripts are the best bet.


    Author Comment

    When I make a SQL script, I find the rows that I need. But it is in view mode, cannot update.. Do I need to write an update row portion?
    LVL 34

    Assisted Solution

    by:Gary Patterson
    One way (and the way I usually do it) is to use the RUN SQL Scripts window in Navigator (right-click int he database name), and first do a SELECT query:

    SELECT *
    FROM table1
    WHERE lastname = 'Patterson' AND firstname = 'Gary'
    Verify that the SELECT gets you the record or records you want to change, and then compose and execute an UPDATE query, cutting and pasting the WHERE clause from your SELECT:

    UPDATE table1
    SET url = ''
    WHERE lastname = 'Patterson' AND firstname = 'Gary'
    Of course you need to be very careful with UPDATE and DELETE queries, since a little slip in syntax can cause a lot of rows to be updated or deleted.  that's why I like the SELECT query first.  Once the WHERE clause is right, if you cut and paste it, you are safe for accidentally selecting the wrong records.

    If you want a full-featured data editor, you'll need to look at third party tools - Navigator's limited interface for data editing leaves a lot to be desired.  I recently used AquaData Studio - it is great (but it costs about $100 a seat, if I remember correctly).

    There are also a plenty of green-screen tools, as DaveSlash indicated:  DFU is installed on most systems, DBU is a popular 3rd party tool, PFE, and many others.

    - Gary Patterson
    Check out my EE profile:


    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    #Citrix #POC #XenDesktop #vCenter #VMware #ESX
    I use more than 1 computer in my office for various reasons. Multiple keyboards and mice take up more than just extra space, they make working a little more complicated. Using one mouse and keyboard for all of my computers makes life easier. This co…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now