Due to my inability to get the spacing right, I didn’t include the subroutine. Yes, it did just move the 3 fields to the new fields.

I did end up just creating the new file that will hold the set of records that I needed. I couldn’t use the CPYF command as it was important to keep the records in the order I was reading them. I also needed to change the M to an N as I was copying the records over.

I am going to try the suggestions you gave me, so I can see if that solves the problem with program described files.

Thanks everyone!

I will try

How do I tell SQL Server 2005 to support more than one language for storing and retrieving data?

…windows 7 clients which are not communicating to a 2003 DC.

There appear to be various difficulties getting Win 7 clients properly connected to a Win 2003 domain. Review Windows 7 clients on 2003 domain / group policy and Windows Small Business Server 2003: Windows 7, Windows Vista and Outlook 2007 compatibility update for example areas to investigate.

Tom

…just do a CPYF command…

I’d suspect the subroutine MOVEREC simply moves the three input fields to the output fields. But without seeing the code, it’s just a guess.

…records with RECTYP = “H”

And do what with records with RECTYP = “N”?

Tom

I have downloaded as400 emulator TN5250 (free trial version).

It might help if you told us which TN5250 emulator you’re using.

Tom

Your EXCEPT lines are specifying a file name not a output exception name.
Try EXCEPT ADD.

Better yet delete this crappy pgm and just do a CPYF command and select only records with RECTYP = “H”

I have degraded one of the PCs to XP and it is communicating to DC. So it is the problem with windows 7 OS .Other 3 PCs are still having the problem. Can any body suggest?

Gigabit ethernet.

I agree with amigus.

I’m pretty sure he’s looking for sample code to connect an application to a database. If this is the case we’ll need to know what language you are writing the application in so we can point you in the right direction.

Cables and switch ports are checked. I am trying to degrade Windows 7 to XP today. there are 4 PCs having issues (differnt hardware ,Lenova,Dell HP). All the PCs are communicating to other network drives. Server is already accessing by around 30 clients. Please advice.

In the world where we live in any one can be an IT geek as long as he wish but in the organization where competition is the situation, specialization is the matther here. In the room before you walk in for your interview might be you MCSE or CCNP would be the certs that talk for you and offcourse that definitely does it all.

When I mget 20120405*.csv, I get:

Path already exists: /home/tms/20120405-103602_LoadMaster_040512103326.csv
Specify REPLACE as a subcommand option.      

How much of the pathname/filename is checked on the IFS? The whole name is unique but the first 19 characters are the same as existing files on the IFS.

Well, the rename is not exactly right.
The server said RNFR not correct. My believe is that it does not like the ‘*’. It seems like the tofile (RNTO) would need to be the whole name of the file ‘ 20120405etc_and_so_on.prx’. I haven’t found any examples on the rename command.
Interactively, I typed rename and hit enter. The server asked for the fromfile name, then the tofile name. It still did not like the ’20120405*.csv’ as the from file name. Maybe rename just will not accept generics.
This will keep me from bringing in all files in the folder each day. I would still love to rename as I pull them (just to prove that I can).

BigKat, I did have a pgm to update the script for the put operation. I copied/changed for the get operation. thx.

UPDATE VINITMB A
SET (a.IFOTH) = (SELECT coalesce (B.IGCOST,0)
FROM ITEMVND1 B
WHERE A.IFITEM = B.IGITEM AND A.ifloc = B.IGLOC AND A.IFDIV = ‘116′)
WHERE EXISTS
(SELECT 1 FROM ITEMVND1 B where b.IgITEM = a.IfITEM AND b.igloc = A.IFLOC)

The UPDATE needs two WHERE clauses.

The first WHERE clause needed will be the one that determines which rows to update. For example:

UPDATE VINITMB A
SET (a.IFOTH) = 'NewVal'
WHERE EXISTS
(SELECT 1 FROM ITEMVND1 B where b.IgITEM = a.IfITEM AND b.igloc = A.IFLOC)

I replaced the subselect with a basic value assignment. I have no idea if it’s a valid value; it merely represents the concept.

The WHERE clause should now be limiting the updated rows only to the rows you need. For this, you could use a SELECT instead of an UPDATE:

SELECT * FROM VINITMB A
WHERE EXISTS
(SELECT 1 FROM ITEMVND1 B where b.IgITEM = a.IfITEM AND b.igloc = A.IFLOC)

The selected rows will show you which ones would be updated if you put that WHERE clause on the UPDATE. If the wrong rows are listed, then keep modifying the WHERE clause until the right result set is defined.

Once that works, you can work on the next WHERE clause.

That one needs to return exactly one value for each row in the result set. There should be no null values returned. Again you could use a SELECT to verify that conditions will work. Maybe something like:

SELECT
 A.IFITEM,
 A.ifloc,
 A.IFDIV,
 a.IFOTH,
 (SELECT coalesce (B.IGCOST,0)
   FROM ITEMVND1 B
   WHERE A.IFITEM = B.IGITEM AND A.ifloc = B.IGLOC AND A.IFDIV = ‘116′) as MyCost
 FROM VINITMB A
 WHERE EXISTS
  (SELECT 1 FROM ITEMVND1 B where b.IgITEM = a.IfITEM AND b.igloc = A.IFLOC)

I don’t know how many unique values there are in your files nor how they match up between your files, so I don’t know what will work. But I do know that you can’t have a successful update until you narrow both WHERE clauses down to give correct results.

It’s a lot safer to test with SELECT, and results are far more visible.

Tom

error cpf1120- user xxxxx does not exist.
i give the correct user id and password

If you get CPF1120 with that appropriate text, then you are reaching some AS/400 and the user profile that you entered does not exist on that AS/400.

There’s no way for us to tell if you’re reaching the correct AS/400.

Tom

Here is what I came up with. …

If only read authority exists, your two-part FTP solution was pretty much what came to mind for me.

Tom

CharlieBrown,
How do you know the file name to rename inside the ftp session?
mget 20120406*.csv
rename 20120406*.csv 20120406*.PRX <<<<would this work?

Kinda a combination of both suggestions.
Nick

CharlieBrown, Bigkat – thanks for the prompt response.
I doubt I have anything but ‘read’ authority on their server. I could try that. That would be a good way.

Bigkat, not sure yet how often this will run. Most likely once a day, late at night for the current day. A guy here had also come up with that (20120406*.csv) plan.

Here is what I came up with.
Two connections.
First- connect, dir, quit. Read through the ftp log, parse the filename, chain out to a file with all previously downloaded filenames. If not found(we haven’t downloaded it before), update/write a different script file with the new-exact file name to get.
Second- connect, get new files, quit. Read the ftp log, parse the filenames, add them to the file with previously downloaded file names.

Then I can kickoff the conversion programs to build db files we can use.
I think it is a plan, just had to think on it a bit.
How does that sound?

You two probably have the easier way and less coding now that I think about it. I will check on the rename authority. If not, I will use the current date to change the script file.

how often does this run???

if daily, you could mget 20120406*.csv
or even hourly, mget 20120406-14*.csv

you would need to have a process update the control script file before you start the FTP session