Buck,
I actually do get an SQL Server error:
Msg 7352, Level 16, State 1, Procedure BME_Batch_Upload2, Line 98
The OLE DB provider "IBMDASQL" for linked server "LS_ISERIES" supplied inconsistent metadata. The object "(user generated expression)" was missing the expected column "Bmk1000".
When I track this error down, it says that the DB Provider IBMDASQL cannot perform the delete (i.e. it's not capable) I've tried other providers, different ODBC data sources and......hours & hours of dinking around. I have to believe that someone has done this by now... FYI I am on V5R4, with current PTF's
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of midrange-l-request@xxxxxxxxxxxx
Sent: Wednesday, December 03, 2014 1:49 PM
To: midrange-l@xxxxxxxxxxxx
Subject: MIDRANGE-L Digest, Vol 13, Issue 1904
Send MIDRANGE-L mailing list submissions to
midrange-l@xxxxxxxxxxxx
To subscribe or unsubscribe via the World Wide Web, visit
http://lists.midrange.com/mailman/listinfo/midrange-l
or, via email, send a message with subject or body 'help' to
midrange-l-request@xxxxxxxxxxxx
You can reach the person managing the list at
midrange-l-owner@xxxxxxxxxxxx
When replying, please edit your Subject line so it is more specific than "Re: Contents of MIDRANGE-L digest..."
*** NOTE: When replying to this digest message, PLEASE remove all text unrelated to your reply and change the subject line so it is meaningful.
Today's Topics:
1. Re: Get Start Date of a Week Number (Booth Martin)
2. iSeries & MSSQL Server (MORE INFO) (Graves, Chuck)
3. RE: Managing libl for multiple 3rd party products
(Steinmetz, Paul)
4. Re: SQuirreL and WWW.PUB1.DE (Holger Scherer)
5. Re: iSeries & MSSQL Server (MORE INFO) (Buck Calabro)
6. RE: JDBC requests (whether from Java or from RPB via JDBCR4):
how does one set a specific "from" IP address when connecting?
(Krill, Coy)
7. Re: SQuirreL and WWW.PUB1.DE (Sam_L)
----------------------------------------------------------------------
message: 1
date: Wed, 03 Dec 2014 14:20:52 -0600
from: Booth Martin <booth@xxxxxxxxxxxx>
subject: Re: Get Start Date of a Week Number
This will give day-of-week:
DayOfWeek = %diff(TargetDate: wTestDate: *days);
DayOfWeek = %rem(DayOfWeek: 7) + 1;
where wTestDate is any past Monday (or use whatever past day of the week you want as # 1).
I didn't test the following but would the week number be the (TargetDate, in Julian Days, plus the offset of 'YYYY-01-04') divided by 7?
That is, for those shops that are shy about SQL?
On 12/3/2014 6:17 AM, John E wrote:
From http://nl.wikipedia.org/wiki/Weeknummer (which is in dutch) :
To calculate the ISO weekday (1=monday) for a given date:
p IsoWkdayNum b export
d IsoWkdayNum pi 10i 0
d date d const
d d s 10i 0
/free
d = %diff(date:d'1970-01-04':*d);
if d < -6;
return 7 - %rem(%abs(d) - 7 : 7);
elseif d < 0;
return 7 + d;
endif;
d = %rem(d : 7);
if d = 0;
return 7;
endif;
return d;
/end-free
p e
To calculate the ISO weeknumber for a given date (using the previous
IsoWkdayNum) :
The year must be from 1584 to 9998, else it returns 0.
p IsoWeekNum b export
d IsoWeekNum pi 10i 0
d date d const
d y s 10i 0
d m s 10i 0
d d s 10i 0
d w1 s 10i 0
d w2 s 10i 0
d wd1 s 10i 0
d wd2 s 10i 0
d wd3 s 10i 0
d wd4 s 10i 0
d wd5 s 10i 0
d x s 10i 0
/free
// http://nl.wikipedia.org/wiki/Weeknummer
--------------------
y = %subdt(date:*y);
if y < 1584
or y > 9998;
return 0;
endif;
m = %subdt(date:*m);
d = %subdt(date:*d);
// UK02
w1 = %div(%diff(date:%date(y*10000+104:*iso):*d):7);
// UK03
wd1 = IsoWkdayNum(date);
// UK04
wd2 = wd1 - %rem(%diff(date:%date((y-1)*10000+104:*iso):*d):7);
if wd2 < 1;
wd2 = wd2 + 7;
endif;
// UK06
w2 = %div(%diff(%date((y-1)*10000+1231:*iso)
:%date((y-1)*10000+104:*iso):*d)
:7);
// UK07
wd3 = wd1 - %rem(%diff(date:%date((y-1)*10000+1231:*iso):*d):7);
if wd3 < 1;
wd3 = wd3 + 7;
endif;
// UK08
x = %diff(date:%date(y*10000+104:*iso):*d);
if x < 0;
wd4 = wd1 - x;
if wd4 > 7;
wd4 = wd4 - 7;
endif;
else;
wd4 = wd1 - %rem(x:7);
if wd4 < 1;
wd4 = wd4 + 7;
endif;
endif;
// UK09
wd5 = wd1 + %rem(%diff(%date((y+1)*10000+104:*iso):date:*d):7);
if wd5 > 7;
wd5 = wd5 - 7;
endif;
// UK11
if m = 1
and d >= 1
and d <= 3;
if wd1 < wd4;
return 1;
else;
// UK10
if wd3 < wd4;
return 1;
elseif wd3 < wd2;
return w2 + 2;
endif;
return w2 + 1;
endif;
elseif m = 12
and d >= 29
and d <= 31
and wd1 < wd5;
return 1;
elseif wd1 < wd4;
return w1 + 2;
endif;
return w1 + 1;
/end-free
p e
The code above is tested, and is correct.
On Wed, Nov 26, 2014 at 3:52 PM, Brian <belstsrv@xxxxxxxxx> wrote:
Hello All,
I have the need to get the date of the first day of the week based on
a week number. I've looked thorough lots of date functions and can't
seem to see a way to get the date of the first day of the week for a
particular week number. Maybe I need a multi-step process, but I am
just struggling to see it.
The date can be based off Sunday or whatever arbitrary day could be
considered as the first day of the week, if that helps.
The data is planning data so I really just need to show it as a
quantity along with a date, but the date needs derived from a week number.
As an example, if I had week number of 1, the date might be
12/29/2014, week 2 would give me 1/5/2015 and so on.
Can anyone provide some suggestions on a method to get to a date
based off the week number?
Thank you.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
http://archive.midrange.com/midrange-l.
--
Booth Martin
www.martinvt.com
(802)461-5349
Skype: booth.martin
Oh dear, I think you'll find reality's on the blink again. -- Marvin the Paranoid Android
------------------------------
message: 2
date: Wed, 3 Dec 2014 20:39:58 +0000
from: "Graves, Chuck" <cgraves@xxxxxxxxxxxxxx>
subject: iSeries & MSSQL Server (MORE INFO)
After more "testing" I have determined is that I CAN clear an entire iSeries file with this statement:
DELETE FROM LS_ISERIES.S650CC6F.RODDATEST.PNMAST_E
After which I can INSERT new data
But this statement fails:
DELETE FROM LS_ISERIES.S650CC6F.RODDATEST.PNMAST_E where BHBTNO = @Batch_Number
[Rodda Paint Company!] Chuck Graves
Director of Information Systems
Rodda Paint Co.<
http://www.roddapaint.com>
6107 N. Marine Drive
Portland, Oregon 97203
(503) 737-6042
------------------------------
message: 3
date: Wed, 3 Dec 2014 21:07:18 +0000
from: "Steinmetz, Paul" <PSteinmetz@xxxxxxxxxx>
subject: RE: Managing libl for multiple 3rd party products
I want to thank everyone for their input.
I also now have a better understanding of the PRD libraries and how they work.
I try to control all interactive environments using a jobd libl, but some apps do not adhere to this.
Any app that has a menu driver system that manipulates the libl on the fly based on menu selections.
I cringe when I see this.
The other thing to keep in mind, before deciding on the solution, is, will the 3rd party app change their library name in a future upgrade.
This could change the solution to be implemented.
The use of these 3rd party support products is mixed, interactive, batch, and/or background tasks.
I've broken them into 3 categories, PRD APPS, PROXY APPS, NEITHER PRD/PROXY
PRD APPS
If an app supports PRD, then I agree the PRD solutions should be used.
Some PRD apps start with a menu, STRPDM, GO BRMS, GO JS,
Other PRD apps will need the library hardcoded, TAATOOL/SWPUSRPRF
I need to Identify all locations where a TAATOOL command is used, and update source inserting TAATOOL/, will be a challenge.
I need to this before I try selling this, not sure how I will do this as of yet.
PROXY APPS
PKZIP, DBU, RDB30
Identify the commands used from these apps and create a PROXY command placed in a SYS library.
The PROXY command is a better solution than a CRTDUPOBJ of the command.
Neither PRD/PROXY
RXS, TCPIP502 (Netsocket/400) , GROUP1, may be more, still researching.
These apps are not cmd driven, library needs to be included in the libl.
Either SYSLIBL (not recommended), JOBD (Preferred), or via ADDLIBLE (least preferred).
If my summary is confusing, misleading, or if I missed anything, please let me know.
Libl, can't live with them, can't live without them.
Paul
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Wednesday, December 03, 2014 7:12 AM
To: Midrange Systems Technical Discussion
Subject: Re: Managing libl for multiple 3rd party products
Look at the PRDLIB parameter on CHGCMD or CRTCMD.
Here's something else to look at.
DSPCMD STRPDM
You'll notice that it isn't a "real" command. It is a "proxy" command.
QSYS/STRPDM only points to QPDA/STRPDM. If you're a wizard at the IFS and are used to this concept in other stream file based operating systems you'll see that it resembles a "symbolic link" in which one directory is just a pointer to another directory. Useful for when one package is configured to find the 'bin' directory under one location and another is configured to expect it under another. And other such cases.
Now, let's look at DSPCMD QPDA/STRPDM
Product library . . . . . . . . . . . : QPDA
And that is why, when you are at the STRPDM menu, and you type in DSPLIBL, you will see
Library Type
QSYS SYS
QSYS2 SYS
QHLPSYS SYS
QUSRSYS SYS
QPDA PRD
ROB CUR
QTEMP USR
QGPL USR
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
http://archive.midrange.com/midrange-l.
------------------------------
message: 4
date: Wed, 3 Dec 2014 22:08:47 +0100
from: Holger Scherer <hs@xxxxxxx>
subject: Re: SQuirreL and WWW.PUB1.DE
Hello Sam,
i heard of some (maybe 5?) users doing so, but mostly they directly access their libraries.
At the moment, PUB1.DE has 69.828 libraries, so it might be possible that there is a >32.768 bug?
-h
Am 03.12.2014 um 19:59 schrieb Sam_L <lennon_s_j@xxxxxxxxxxx>:
Has anyone successfully connected to the free AS/400 WWW.PUB1.DE using the SQuirreL client? If so, do you see the schemas?
I have successfully used SQuirreL on a couple of machines, but with PUB1.DE I cannot see the schemas. I suspect a bug in JT400 that can?t handle the large number of libraries on PUB1.DE. (I think there are 35,000+ libraries.)
Sam
------------------------------
message: 5
date: Wed, 03 Dec 2014 16:24:14 -0500
from: Buck Calabro <kc2hiz@xxxxxxxxx>
subject: Re: iSeries & MSSQL Server (MORE INFO)
On 12/3/2014 3:39 PM, Graves, Chuck wrote:
After more "testing" I have determined is that I CAN clear an entire iSeries file with this statement:
DELETE FROM LS_ISERIES.S650CC6F.RODDATEST.PNMAST_E
After which I can INSERT new data
But this statement fails:
DELETE FROM LS_ISERIES.S650CC6F.RODDATEST.PNMAST_E where BHBTNO = @Batch_Number
I assume that the failure means 'appears to work but the row is not
deleted', because I think that if you had an error message you'd have
included that. So, it seems likely that your statement is executing and
not finding any rows to delete.
Check the contents of @Batch_Number and make sure it exactly matches the
contents of one or more rows. By exactly, I mean length, format and
contents. Double check your assumption that there is a row to be
deleted by doing a count(*) of the rows with the same WHERE clause.
--
--buck
'I had nothing to offer anybody except my own confusion' - Jack Kerouac
------------------------------
message: 6
date: Wed, 3 Dec 2014 21:48:11 +0000
from: "Krill, Coy" <CKrill@xxxxxxxxxxx>
subject: RE: JDBC requests (whether from Java or from RPB via JDBCR4):
how does one set a specific "from" IP address when connecting?
Jump into QSHELL and run this:
java -cp /qibm/proddata/os400/jt400/lib/jt400.jar utilities.JPing <ip address or dns name of server>
I "believe" this is actually better than just a ping or portscan and it actually connects to service as a client, so you don't get a false positive for something else running on the port.
If you run WRKREGINF and check the QIBM_QZDA_* entries you can see if a program is currently registered to the exit, it will have *YES under the registered column. Put an 8 next to anything that has *YES and you will see what program is hooked off that exit.
Coy Krill
Core Processing Administrator/Analyst
Washington Trust Bank
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of James H. H. Lampert
Sent: Wednesday, December 03, 2014 11:42
To: Midrange Systems Technical Discussion
Subject: Re: JDBC requests (whether from Java or from RPB via JDBCR4): how does one set a specific "from" IP address when connecting?
Importance: Low
On 12/3/14 10:30 AM, Krill, Coy wrote:
Have you verified that the box that's not receiving the connections
has the appropriate host servers running? And if they are running are
there any exit points to monitors that might be denying the
connections based on user or source ip?
I forgot to mention: Yes the relevant Host Servers (or at least SOMETHING using the relevant port numbers) appear to be running. (Is there a good way to verify that the ports really are running Host Servers, and not something else?
And I wasn't aware that there were any exit hooks for that, but I can investigate after lunch. How would I check for them? The only exit hooks I've ever used are the ones in our own CRM product, and they work somewhat differently from the way they work in IBM products.
--
JHHL
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
http://archive.midrange.com/midrange-l.
=====================================================================================================
This electronic mail message and any attachments may contain confidential or privileged
information and is intended for use solely by the above-referenced recipient. Any review,
copying, printing, disclosure, distribution, or other use by any other person or entity is
strictly prohibited under applicable law. If you are not the named recipient, or believe
you have received this message in error, please immediately notify the sender by replying
to this message and delete the copy you received. =====================================================================================================
------------------------------
message: 7
date: Wed, 3 Dec 2014 15:49:03 -0600
from: Sam_L <lennon_s_j@xxxxxxxxxxx>
subject: Re: SQuirreL and WWW.PUB1.DE
Holger,
I can connect and use SQuirreL, but I can't get it to list the schemas.
Having the schemas make building SQL easier using autocomplete.
The SQuirreL developer gave me a short java program that (probably)
shows where the problem occurs. I'll have to see if I can get someone
to run it against their machine which has less than 32,768 libraries.
Sam
On 12/3/2014 3:08 PM, Holger Scherer wrote:
Hello Sam,
i heard of some (maybe 5?) users doing so, but mostly they directly access their libraries.
At the moment, PUB1.DE has 69.828 libraries, so it might be possible that there is a >32.768 bug?
-h
Am 03.12.2014 um 19:59 schrieb Sam_L <lennon_s_j@xxxxxxxxxxx>:
Has anyone successfully connected to the free AS/400 WWW.PUB1.DE using the SQuirreL client? If so, do you see the schemas?
I have successfully used SQuirreL on a couple of machines, but with PUB1.DE I cannot see the schemas. I suspect a bug in JT400 that can?t handle the large number of libraries on PUB1.DE. (I think there are 35,000+ libraries.)
Sam
---
This email has been checked for viruses by Avast antivirus software.
http://www.avast.com
------------------------------
Subject: Digest Footer
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) digest list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
http://archive.midrange.com/midrange-l.
------------------------------
End of MIDRANGE-L Digest, Vol 13, Issue 1904
********************************************
[Rodda Paint Company!]
Chuck Graves
Director of Information Systems
Rodda Paint Co.<
http://www.roddapaint.com>
6107 N. Marine Drive
Portland, Oregon 97203
(503) 737-6042
midrange.com
