How do I INSERT records into AS400 DB2 file from SQL server?

75 pts.
Tags:
AS/400
DB2/400
SQL Server
SQL Server 2005
I can pull data from AS400 to SQL but cannot get my command on the SQL server to push the data back.  This is a very small process, I only need an item number and date.

Also, the date on the SQL db is "datetime".  I'll need to convert that to a more recognizable date field to put in the AS400 db.

To be clear, the connections are in place, I have a linked server set up, and the db2 file is journaled.

Any assistance would be appreciated!



Software/Hardware used:
AS400, SQL Server 20005

Answer Wiki

Thanks. We'll let you know when a new response is added.

Insert requires values of the correct type for all fields except those that allow nulls.
It can use the default column sequence or you can include the column list.
All fields of the primary key must have values and they must provide a unique key.
Any other file constraints, foreign keys, unique keys and other constraints must be fulfilled.
You should be looking at the sql error when you try to insert a row.

So you say you only need to put in one or two fields. That’s all the fields in the file? So us the file structure and your insert command. Tell us your error. We’re trying to help.
Phil

Discuss This Question: 11  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    What are you using to make the connection (e.g., Host Integration Server 2004, or OLE DB Provider for DB2, or...)? What OS version is on the AS/400? As you might know, DB2 allows three different column types -- DATE, TIME and TIMESTAMP -- while SQL Server uses DATETIME for all three. Also, though DATETIME is mostly equivalent to TIMESTAMP, there is an implementation difference in that DB2 generally stores an additional three digits of accuracy. When communicating between the two, it's generally necessary to perform a conversion against the values in the columns to get a corresponding value in the other database. The specific conversion depends on the column data types. Under some circumstances, it's possible to have the conversion done for you by a property in the connection string. The specific products being used, and the fixes applied to those products, can determine if connection properties can help. If someone already has solved the combination that you have, a working example might be quickly available. Tom
    125,585 pointsBadges:
    report
  • Normap22
    Thanks TomLiotta! AS400 = V5R4M0 SQL Server = 2005 Connection = Microsoft OLE DB Provider for ODBC Connection Then I linked the server via SQL 2005, Server Objects, Linked Servers. Previously, when I brought data into the sql db, I took a "mm-dd-yy" alpha field and used a CAST conversion to get it into my sql smalldatetime. I am not concerned with the time at all, just the date.
    75 pointsBadges:
    report
  • philpl1jb
    One critical thing we need is the actualy data type of the date field on the 400 side. When you said that you brought it in as char mm-dd-yy is that because it's a character field in the 400? Phil
    51,355 pointsBadges:
    report
  • TomLiotta
    “mm-dd-yy” alpha field Can you elaborate on that? Phil's right. Is that actually an 8-byte character field that contains date values in that format? Tom
    125,585 pointsBadges:
    report
  • Normap22
    The big picture here is the INSERT into the file on the AS400. Taking the date out of it, how can I insert? I can change my date field to any field format later but right now I cannot even insert the item number. But yes, to get the data into SQL, my AS400 date was an 8 character alpha field where 09/15/2010 was in the file as "09-15-10". I performed a cast function on the insert into the SQL table to get the date into smalldatetime format - That worked just great. The SQL table that I'm pulling from has the date in the datetime format. I assume I'll have to do some type of convert to get it into some type of format that I can bork with. Thnks, Norma
    75 pointsBadges:
    report
  • TomLiotta
    The big picture here is the INSERT into the file on the AS400. Taking the date out of it, how can I insert? My apologies. It seemed as if the date was where your insert was getting hung up. In that case, the question is now less clear. You do an insert by coding and executing a SQL INSERT statement. But because that's such an obvious answer, there must be something else that you are asking about. Why isn't INSERT working for you? Are you seeing an error message? What error? One potential problem is that your connection doesn't have properties that allow updates. Is the file journaled on your AS/400? Tom
    125,585 pointsBadges:
    report
  • Normap22
    First of all, I really appreciate your help, I'm in the dark on this! The obvious to some people is totally obscured to others. :-} I am not a SQL kind of person which is why I'm having so many problems. I think I have to do something like this:
    INSERT OPENQUERY (WMT400, 'SELECT rllotn, rlitem, rlmm, rldd, rlyy FROM rmtlib.rmtfile')
    VALUES ('select lotnum, itemnumber, datepart(mm,manufactured) as F1, datepart(dd,manufactured) as F2, datepart(YY,manufactured) as F3
    from lots')
    BUT I just don't know the correct way to format the command. The remote file on WMT400 is in rmtlib and is rmtfile. The fields are rllotn=20A, rlitem=30A, rlmm=2 signed numeric, rldd=2 signed numeric, rlyy=4 signed numeric. The SQL table is lots and has LotNum=20A, ItemNumber=30A, manufactured=datetime. Using datepart gives me each individual piece in (I think) numeric format. Here is my error: Msg 213, Level 16, State 1, Line 1 Insert Error: Column name or number of supplied values does not match table definition. I also tried the same command to a file where the remote file had RLMM, RLDD, RLYY as alpha fields and tried the command that way too. Same error. One potential problem is that your connection doesn’t have properties that allow updates. Is the file journaled on your AS/400? I do not know how to check the properties to see if I can do updates on the 400. It IS journaled. Norma
    75 pointsBadges:
    report
  • Normap22
    Ok, I answered one question, I CAN update the 400 from SQL. I tried this.... INSERT OPENQUERY (WMT400, 'SELECT rllotn, rlitem, rlmm, rldd, rlyy FROM crpdta.frobarlot') VALUES ('A10101', 'B20202', 02, 22, 1963) and inserted 1 row of static data into the AS400 file. What I need to know is how to set up the "VALUES" part of my sql command. Does this help?
    75 pointsBadges:
    report
  • Normap22
    THANKS TO TOM AND PHILLIP FOR ALL OF YOUR HELP! As I said, I'm not very knowledgeable about SQL; I didn't need the VALUES statement, did I? :-) THIS worked perfectly: INSERT OPENQUERY (WMT400, 'SELECT rllotn, rlitem, rlmm, rldd, rlyy FROM rmtlib.rmtfile') Select lotnum, itemnumber, datepart(mm,manufactured) as F1, datepart(dd,manufactured) as F2, datepart(YY,manufactured) as F3 from lots Finally, I can make progress on my project!
    75 pointsBadges:
    report
  • TomLiotta
    As I said, I’m not very knowledgeable about SQL;... Even more of a problem, you're not actually using "SQL". You're using Microsoft's functions that do things in their own ways. There's nothing wrong with that; it just isn't quite how an INSERT would be done in standard SQL for AS/400s which tend to follow actual SQL standards much more closely in supported statements. I'm really sorry it took so much to get around to the real problems. For future questions, begin with pasting a copy of some actual attempt at coding a solution. Add a description of the environment that includes any client and/or server operating system levels, any client/server products that might be in use for the problem, and any specific error messages. If there are message identifiers that you can see, those can be more important than the message text. If you don't know an important detail, make sure you ask how to track it down. Learning how to find that stuff out can be more important than just getting a direct answer. (Sometimes the wrong question might be asked, so a direct answer could just put you farther down the wrong path even if the answer is totally 'correct'.) Nevertheless, glad something is working for you! Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    Thank you for responding. The code doesn't look anything like I expected, but then it's been about 6 years and a couple of versions since I worked with SQL/Server. Phil
    51,355 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following