Why SQL2036N error (path not valid) when Load utility uses SMB networkdrive as source ?

[Eric.Jones]

I've encountered a strange error with loading delimited files from a
Samba (SMB) network drive, has anyone else seen this before?

(Platform: WinXP Pro, UDB PE 8015, level 02060106, SAMPLE db)

db2 load from M:\org1.del of del replace into org
SQL3109N The utility is beginning to load data from file "M:\org1.del".
SQL2036N The path for the file or device "M:\org1.del" is not valid.

db2diag.log:
2004-07-27-17.59.37.156000 Instance:DB2 Node:000
PID:1080(db2syscs.exe) TID:2228 Appid:none
database utilities sqluMCCheckDevType Probe:10
Media controller -- invalid device path: M:\org1.del

The Samba drives are served from an AIX host, and seem to work fine
otherwise. I created the data file a few moments before, using an export
command and it completed fine. I can also read from this drive with the
import command, and it processes the records without problem.

C:\>net use
Status Lokal Remote Netzwerk
-----------------------------------------------------------------
OK M: \\spsmbp\data_tmp Microsoft Windows-Netzwerk
A db2trace shows the following:

17741 data DB2 oper system services sqloFileAttrib cei
(3.3.15.191.2.2)
pid 2480 tid 2052 cpid -1 node 0 sec 0 nsec 4229608 probe 2
bytes 11
4D3A5C6F 7267312E 64656C M:\org1.del

17744 exit DB2 oper system services sqloFileAttrib cei (2.3.15.191.2)
pid 2480 tid 2052 cpid -1 node 0 sec 0 nsec 4229757
rc = 0x870F0011 = -2029060079 = SQLO_PATH

17745 data DB2 database utilities sqluMCCheckDevType fnc
(3.3.21.1275.0.10)
pid 2480 tid 2052 cpid -1 node 0 sec 0 nsec 4229768 probe 10
bytes 52
4D656469 6120636F 6E74726F 6C6C6572 Media controller
202D2D20 696E7661 6C696420 64657669 -- invalid devi
63652070 6174683A 204D3A5C 6F726731 ce path: M:\org1
2E64656C .del

Seems like the Media controller didn't like the answer returned by the
sqloFileAttrib function, but why? (SQLO_PATH?) I didn't find any
documented limitations for Load using network drives, so I am wondering
if the Samba server could be at fault.

The share definition in smb.conf seems normal, although Oplocks=no is
set. Would Load need byte-range locking on the input file? But not Import ??

Thanks for any feedback...

[Philip Sherman]

I'm not sure why EXPORT works and LOAD fails but I've run into a similar
problem with networked drives.

UDB is started fairly early in the boot process - usually before the
networked drives have been defined to the system. Things that go through
your user logon (ie. EXPORT) can use the networked drive because you can
see it. UDB itself has no access to the networked drives because they
didn't exist at the time it started.

This obviously isn't what we'd like to see but I think that it's a
characteristic of the Win environment.

Phil Sherman

Eric.Jones wrote:

I've encountered a strange error with loading delimited files from a
Samba (SMB) network drive, has anyone else seen this before?

(Platform: WinXP Pro, UDB PE 8015, level 02060106, SAMPLE db)

db2 load from M:\org1.del of del replace into org
SQL3109N The utility is beginning to load data from file "M:\org1.del".
SQL2036N The path for the file or device "M:\org1.del" is not valid.

db2diag.log:
2004-07-27-17.59.37.156000 Instance:DB2 Node:000
PID:1080(db2syscs.exe) TID:2228 Appid:none
database utilities sqluMCCheckDevType Probe:10
Media controller -- invalid device path: M:\org1.del

The Samba drives are served from an AIX host, and seem to work fine
otherwise. I created the data file a few moments before, using an export
command and it completed fine. I can also read from this drive with the
import command, and it processes the records without problem.

C:\>net use
Status Lokal Remote Netzwerk
-----------------------------------------------------------------
OK M: \\spsmbp\data_tmp Microsoft Windows-Netzwerk
A db2trace shows the following:

17741 data DB2 oper system services sqloFileAttrib cei
(3.3.15.191.2.2)
pid 2480 tid 2052 cpid -1 node 0 sec 0 nsec 4229608 probe 2
bytes 11
4D3A5C6F 7267312E 64656C M:\org1.del

17744 exit DB2 oper system services sqloFileAttrib cei (2.3.15.191.2)
pid 2480 tid 2052 cpid -1 node 0 sec 0 nsec 4229757
rc = 0x870F0011 = -2029060079 = SQLO_PATH

17745 data DB2 database utilities sqluMCCheckDevType fnc
(3.3.21.1275.0.10)
pid 2480 tid 2052 cpid -1 node 0 sec 0 nsec 4229768 probe 10
bytes 52
4D656469 6120636F 6E74726F 6C6C6572 Media controller
202D2D20 696E7661 6C696420 64657669 -- invalid devi
63652070 6174683A 204D3A5C 6F726731 ce path: M:\org1
2E64656C .del

Seems like the Media controller didn't like the answer returned by the
sqloFileAttrib function, but why? (SQLO_PATH?) I didn't find any
documented limitations for Load using network drives, so I am wondering
if the Samba server could be at fault.

The share definition in smb.conf seems normal, although Oplocks=no is
set. Would Load need byte-range locking on the input file? But not
Import ??

Thanks for any feedback...

[Mark A]

"Philip Sherman" <ps******@ameritech.net> wrote in message
news:zx****************@newssvr28.news.prodigy.com ...

I'm not sure why EXPORT works and LOAD fails but I've run into a similar
problem with networked drives.

UDB is started fairly early in the boot process - usually before the
networked drives have been defined to the system. Things that go through
your user logon (ie. EXPORT) can use the networked drive because you can
see it. UDB itself has no access to the networked drives because they
didn't exist at the time it started.

This obviously isn't what we'd like to see but I think that it's a
characteristic of the Win environment.

Phil Sherman

I believe that these limitations are documented in the Command Reference
manual for LOAD.

[Mark Yudkin]

Drive mappings are local to the logon account that creates them. On W2K and
earlier, they were also machine-wide aliases, despite being local to the
logon account - a problem that was first resolved in XP. That problem
prevented distinct mappings of the same drive letter using different logon
accounts.

As a result M: does not exist in the DB2 logon account, where the load is
running, and the error message correctly states this. Export works, as the
export is executed in the client process.

Neither SAMBA, nor DB2 LOAD are in any way involved. This is a Windows
issue - or more precisely, a problem with your understanding of Windows
security.

The fix in XP (W2k3) does not help your case, as what you want is global
aliases and no security, whereas what is fixed is that aliases are now
logon-session local, rather than just beig local to the terminal session.
This means that the drive alias is now scoped to the same object to which
security was tied. In any case, it would obviously be a very bad thing
indeed if DB2 were to see your drive mappings and be able to access your
drives as if it were you. This would be equivalent to no security.
"Eric.Jones" <Er********@hispeed.ch> wrote in message
news:ce**********@newshispeed.ch...

I've encountered a strange error with loading delimited files from a
Samba (SMB) network drive, has anyone else seen this before?

(Platform: WinXP Pro, UDB PE 8015, level 02060106, SAMPLE db)

db2 load from M:\org1.del of del replace into org
SQL3109N The utility is beginning to load data from file "M:\org1.del".
SQL2036N The path for the file or device "M:\org1.del" is not valid.

db2diag.log:
2004-07-27-17.59.37.156000 Instance:DB2 Node:000
PID:1080(db2syscs.exe) TID:2228 Appid:none
database utilities sqluMCCheckDevType Probe:10
Media controller -- invalid device path: M:\org1.del

The Samba drives are served from an AIX host, and seem to work fine
otherwise. I created the data file a few moments before, using an export
command and it completed fine. I can also read from this drive with the
import command, and it processes the records without problem.

C:\>net use
Status Lokal Remote Netzwerk
-----------------------------------------------------------------
OK M: \\spsmbp\data_tmp Microsoft Windows-Netzwerk
A db2trace shows the following:

17741 data DB2 oper system services sqloFileAttrib cei
(3.3.15.191.2.2)
pid 2480 tid 2052 cpid -1 node 0 sec 0 nsec 4229608 probe 2
bytes 11
4D3A5C6F 7267312E 64656C M:\org1.del

17744 exit DB2 oper system services sqloFileAttrib cei (2.3.15.191.2)
pid 2480 tid 2052 cpid -1 node 0 sec 0 nsec 4229757
rc = 0x870F0011 = -2029060079 = SQLO_PATH

17745 data DB2 database utilities sqluMCCheckDevType fnc
(3.3.21.1275.0.10)
pid 2480 tid 2052 cpid -1 node 0 sec 0 nsec 4229768 probe 10
bytes 52
4D656469 6120636F 6E74726F 6C6C6572 Media controller
202D2D20 696E7661 6C696420 64657669 -- invalid devi
63652070 6174683A 204D3A5C 6F726731 ce path: M:\org1
2E64656C .del

Seems like the Media controller didn't like the answer returned by the
sqloFileAttrib function, but why? (SQLO_PATH?) I didn't find any
documented limitations for Load using network drives, so I am wondering
if the Samba server could be at fault.

The share definition in smb.conf seems normal, although Oplocks=no is
set. Would Load need byte-range locking on the input file? But not Import ??
Thanks for any feedback...

[Eric.Jones]

The share definition in smb.conf seems normal, although Oplocks=no is
set. Would Load need byte-range locking on the input file? But not
Import ??

After a bit more investigation, it seems Samba is not (directly)
implicated. Using Ethereal to monitor the network traffic to the SMB
server, we saw zero packets exchanged during the open attempt. So it's
not failing because of any bad status returned by Samba.

With the feedback from Philip & Mark (thanks guys!), it seems the real
issue lies with Windows network share handling. Although the DB2
instance owner is the same as the login account I am using for export,
it is normally started earlier as a windows service. I'm going to try
some further testing with the focus on sqloFileAttrib.

The original goal was to provide a common filespace for data
distribution to a group of developer machines. The delimited load files
for numerous testcases could be served from Samba and loaded
automatically, without requiring lots of local space on every machine.

With the apparent evolution of "network drive letter" transitioning to
"logon session resource", this may not be a viable approach for Windows
targets.

[Mike]

I have just recently found a issue that may help solve this problem. I
was having problems doing restores and backups to a networked drive
running db2 on win2k3. Anyone else having this issue? I previously did
this with no problem against win2k using mapped drives and service
accounts with domain rights. I was getting the same SQL code you are
getting. Anyway, long story short...Try using UNC paths instead of
mapped drives. That seemed to be the answer for my problem.
"Eric.Jones" <Er********@hispeed.ch> wrote in message news:<ce**********@newshispeed.ch>...

The share definition in smb.conf seems normal, although Oplocks=no is
set. Would Load need byte-range locking on the input file? But not
Import ??

After a bit more investigation, it seems Samba is not (directly)
implicated. Using Ethereal to monitor the network traffic to the SMB
server, we saw zero packets exchanged during the open attempt. So it's
not failing because of any bad status returned by Samba.

With the feedback from Philip & Mark (thanks guys!), it seems the real
issue lies with Windows network share handling. Although the DB2
instance owner is the same as the login account I am using for export,
it is normally started earlier as a windows service. I'm going to try
some further testing with the focus on sqloFileAttrib.

The original goal was to provide a common filespace for data
distribution to a group of developer machines. The delimited load files
for numerous testcases could be served from Samba and loaded
automatically, without requiring lots of local space on every machine.

With the apparent evolution of "network drive letter" transitioning to
"logon session resource", this may not be a viable approach for Windows
targets.

[Eric.Jones]

Mike wrote:

[snip]..Anyway, long story short...Try using UNC paths instead of mapped drives. That seemed to be the answer for my problem.

Thanks for relating the backup/restore experiences.
I tried the UNC variation in my short testcase, but also without success.
However, I was able to expose a bit more information using filemon to
view some of the underlying calls.

C:\>db2 load from \\spsmbp\data_tmp\org1.del of del messages org1.msg replace into org

SQL3109N The utility is beginning to load data from file "\\spsmbp\data_tmp\org1.del".
SQL2036N The path for the file or device "\\spsmbp\data_tmp\org1.del" is not valid.

The db2diag.log shows the same complaint from MediaController:

2004-08-11-11.08.44.437000 Instance:DB2 Node:000
PID:2444(db2syscs.exe) TID:2184 Appid:none
database utilities sqluMCCheckDevType Probe:10
Media controller -- invalid device path: \\spsmbp\data_tmp\org1.del

Here's what was happening from the XP filemon trace:
(expanding mail line width to 100, hope it's readible)
:27.843 db2bp.exe:3912 FASTIO_QUERY_OPEN C:\org1.msg SUCCESS Attributes: A
:27.843 db2bp.exe:3912 IRP_MJ_CREATE C:\org1.msg SUCCESS Options: OpenIf WriteThrough Access: All
:27.843 db2bp.exe:3912 IRP_MJ_CREATE \\spsmbp\data_tmp SUCCESS Options: Open Directory Access: Traverse
:27.843 db2bp.exe:3912 IRP_MJ_QUERY_VOLUME_INFORMATION \\spsmbp\data_tmp SUCCESS FileFsDeviceInformation
..
:28.140 db2syscs.exe:1912 FASTIO_QUERY_OPEN \\spsmbp\data_tmp\org1.del FAILURE
:28.140 db2syscs.exe:1912 IRP_MJ_CREATE \\spsmbp\data_tmp\org1.del * 0xC000006D Options: Open Access:All
..
:28.156 db2syscs.exe:1912 FASTIO_QUERY_OPEN \\spsmbp\data_tmp\org1.del FAILURE
:28.156 db2syscs.exe:1912 IRP_MJ_CREATE \\spsmbp\data_tmp\org1.del * 0xC000006D Options: Open Access:All

Notice how the db2bp process has no trouble with the open and volume query.
But this is a local process started during my login session from the CLI window.
Later db2syscs (engine process?) tries (twice!) with FASTIO_QUERY_OPEN but fails.

The IRP_MJ_Create appears to be a filter for the underlying FSD (or UNC redirector).
The 0xC..06D error seems related to Logon Authentication Failure problems, which
suggests why the open does not get a handle returned. I could find very little info
documenting the FASTIO_ functions, so I gave up at this point with WinDDK debug.

[Karl]

Eric.Jones wrote:

Mike wrote:

[snip]..Anyway, long story short...Try using UNC paths instead of

mapped drives. That seemed to be the answer for my problem.

Thanks for relating the backup/restore experiences.
I tried the UNC variation in my short testcase, but also without success.
However, I was able to expose a bit more information using filemon to
view some of the underlying calls.

C:\>db2 load from \\spsmbp\data_tmp\org1.del of del messages org1.msg
replace into org

SQL3109N The utility is beginning to load data from file
"\\spsmbp\data_tmp\org1.del".
SQL2036N The path for the file or device "\\spsmbp\data_tmp\org1.del" is
not valid.

The db2diag.log shows the same complaint from MediaController:

2004-08-11-11.08.44.437000 Instance:DB2 Node:000
PID:2444(db2syscs.exe) TID:2184 Appid:none
database utilities sqluMCCheckDevType Probe:10
Media controller -- invalid device path: \\spsmbp\data_tmp\org1.del

Here's what was happening from the XP filemon trace:
(expanding mail line width to 100, hope it's readible)

:27.843 db2bp.exe:3912 FASTIO_QUERY_OPEN C:\org1.msg SUCCESS
Attributes: A
:27.843 db2bp.exe:3912 IRP_MJ_CREATE C:\org1.msg SUCCESS
Options: OpenIf WriteThrough Access: All
:27.843 db2bp.exe:3912 IRP_MJ_CREATE \\spsmbp\data_tmp SUCCESS
Options: Open Directory Access: Traverse
:27.843 db2bp.exe:3912 IRP_MJ_QUERY_VOLUME_INFORMATION
\\spsmbp\data_tmp SUCCESS FileFsDeviceInformation
.. :28.140 db2syscs.exe:1912 FASTIO_QUERY_OPEN
\\spsmbp\data_tmp\org1.del FAILURE
:28.140 db2syscs.exe:1912 IRP_MJ_CREATE \\spsmbp\data_tmp\org1.del
* 0xC000006D Options: Open Access:All
..
:28.156 db2syscs.exe:1912 FASTIO_QUERY_OPEN
\\spsmbp\data_tmp\org1.del FAILURE
:28.156 db2syscs.exe:1912 IRP_MJ_CREATE \\spsmbp\data_tmp\org1.del
* 0xC000006D Options: Open Access:All

Notice how the db2bp process has no trouble with the open and volume query.
But this is a local process started during my login session from the CLI
window.
Later db2syscs (engine process?) tries (twice!) with FASTIO_QUERY_OPEN
but fails.

The IRP_MJ_Create appears to be a filter for the underlying FSD (or UNC
redirector).
The 0xC..06D error seems related to Logon Authentication Failure
problems, which
suggests why the open does not get a handle returned. I could find very
little info
documenting the FASTIO_ functions, so I gave up at this point with
WinDDK debug.

does the account which is starting the db2 service authorized to access
this unc path? this was the solution to my backup/restore problem with
unc paths.